Threshold using Aggr with Multiple Dimensions

Anonymous — Mon, 07 Jul 2014 22:34:58 GMT

I have a chart where the user first selects a threshold (variance %) and this should limit the rows that appear in the chart to the ones that meet this limit. I used a calculated dimension with an AGGR function to get this to work and it works beautifully when there is only one dimension. But I need to have more than one dimension, and this expression breaks down, adding extra rows (beyond the threshold) to the chart. How can I correctly adjust this expression to allow for the added dimensions?

This is the working expression with a single dimension:

=aggr(if((sum({<Year={$(=max(Year))}>}m.extrev)/
sum({1<Year={$(=max(Year)-1)}>*<Year=,%_invdate={"<=$(=addmonths(max(%_invdate),-12))"}>}m.extrev))-1<=$(Variance%)
,Brand)
,Brand)

I tried this and got extra rows:

=aggr(if((sum({<Year={$(=max(Year))}>}m.extrev)/
sum({1<Year={$(=max(Year)-1)}>*<Year=,%_invdate={"<=$(=addmonths(max(%_invdate),-12))"}>}m.extrev))-1<=$(Variance%)
,Brand)
,Brand, [Item Type])

Any ideas?

topic Threshold using Aggr with Multiple Dimensions in QlikView

Threshold using Aggr with Multiple Dimensions