Calculated Dimension in A list Box

[#1]

Hi

I have a variable which calculated the sum(Actual)/sum(plan) to reflect consumption. How coudl I create a list which help filter out consumption which below.9 and between .9 and 110 and greater than 110. The filter should be under consumption, right consumption and over consumption

Consumption Status
Over
Right
Under

when i click on the list only fields that meet the conditions are applied

Regards,

[Nicole-Smith]

You'll need to do the calculation in the load script and add your new [Consumption Status] field there based on your thresholds.

[hic]

A calculated field must be linked to a real field - one way or another. In your case this field could e.g. be Product or Department or something similar. It should be the dimension for which the Sum(Sales)/Sum(Plan) is over or under.

One way to do it, is to use the Aggr() function:

=Aggr(If(Sum(Sales)/Sum(Plan)>1,'Over','Under'),Product)

HIC