topic Re: Error in Calculated Dimension with AGGR function in QlikView

Error in Calculated Dimension with AGGR function

Mon, 15 Jul 2013 11:08:34 GMT

I'm trying to implement a calculated dimension into a chart using an AGGR function.

I have a list of total revenue for different products, and I want to group them to see what proportions of the products fall into each group.

Below is the code I am trying to use to calculate the dimension.

=if(sum(aggr(sum({$<Year={$(vYearSel_1)}ActualRevenue),Product))>100000,'>100k',
if(sum(aggr(sum({$<Year={$(vYearSel_1)}ActualRevenue),Product))>200000,'>200k','Other'))

vYearSel_1 is just a variable for the previous year

Does anyone know why this will not work?

Appreciate any help!

Re: Error in Calculated Dimension with AGGR function

Anonymous — Mon, 15 Jul 2013 12:42:25 GMT

Try to enclose the whole expresion in another aggr()

Re: Error in Calculated Dimension with AGGR function

Mon, 15 Jul 2013 12:45:46 GMT

Thanks for getting back to me Michael.

So like this?

=aggr(if(sum(aggr(sum({$<Year={$(vYearSel_1)}ActualRevenue),ProductName))>100000,'>100k',
if(sum(aggr(sum({$<Year={$(vYearSel_1)}ActualRevenue),ProductName))>200000,'>200k','Other')))

This doesn't seem to work either. The expression I am using is simply =Count(Distinct(ProductName))

in case that is relevant...

Re: Error in Calculated Dimension with AGGR function

Anonymous — Mon, 15 Jul 2013 12:55:44 GMT

You added aggr() incorrectly. It must always contain the field(s) you're aggregating by, e.g.:

aggr(<your expression>, ProductName)

Re: Error in Calculated Dimension with AGGR function

Mon, 15 Jul 2013 13:01:53 GMT

So:

=aggr(if(sum(aggr(sum({$<Year={$(vYearSel_1)}ActualRevenue),ProductName))>100000,'>100k',
if(sum(aggr(sum({$<Year={$(vYearSel_1)}ActualRevenue),ProductName))>200000,'>200k','Other')),ProductName)

That still doesn't appear to be working...

Thanks for the help, though. Much appreciated.

Re: Error in Calculated Dimension with AGGR function

Anonymous — Mon, 15 Jul 2013 13:06:40 GMT

It should be that's complex. Maybe try to simplify it, remove one sum and one aggr (count my parenthesis please):

=aggr(if(sum({$<Year={$(vYearSel_1)}ActualRevenue)>100000,'>100k',
if(sum({$<Year={$(vYearSel_1)}ActualRevenue)>200000,'>200k','Other')),ProductName)

Can you upload a sample if it doesn't work?

Re: Error in Calculated Dimension with AGGR function

Mon, 15 Jul 2013 13:29:27 GMT

No this still hasn't worked. I can't upload any of the file for confidentiality reasons.

Data would essentially be a list of sales:

LOAD Month,

Year,

ProductName,

ActualRevenue

From [SourceTable]

Thanks again

Re: Error in Calculated Dimension with AGGR function

Anonymous — Mon, 15 Jul 2013 13:42:36 GMT

Yes, I had a few mistypings... Removed too much. This should work:

=aggr(if(sum({$<Year={$(vYearSel_1)}>} ActualRevenue)>100000,'>100k',
if(sum({$<Year={$(vYearSel_1)}>} ActualRevenue)>200000,'>200k','Other')),ProductName)

PS: And, you have to fix the logic. If you check >100K first, there will be no check for >200K. So, use the >200 in the beginning

Re: Error in Calculated Dimension with AGGR function

Mon, 15 Jul 2013 14:49:46 GMT

Thanks very much - that's very helpful.

If I also wanted to include another criteria in the set analysis - so that Product_Flag={1}, the chart no longer works.

Also - I get some Products being grouped in none of the three categories....

Re: Error in Calculated Dimension with AGGR function

Anonymous — Mon, 15 Jul 2013 17:01:43 GMT

I expect this to work:

=aggr(if(sum({$<Year={$(vYearSel_1)}, Product_Flag={1} >} ActualRevenue)>200000,'>200k',

if(sum({$<Year={$(vYearSel_1)}, Product_Flag={1}>} ActualRevenue)>100000,'>100k','Other')),ProductName)

As for the wrong Product groups - it depends on how the conditions (IFs) are designed. Can't tell more without the specific examples of data...