9 Replies Latest reply: Jul 15, 2013 1:02 PM by Michael Solomovich

# Error in Calculated Dimension with AGGR function

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

Try to enclose the whole expresion in another aggr()

• ###### Re: Error in Calculated Dimension with AGGR function

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

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

• ###### Re: Error in Calculated Dimension with AGGR function

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

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

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:

Year,

ProductName,

ActualRevenue

From [SourceTable]

Thanks again

• ###### Re: Error in Calculated Dimension with AGGR function

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

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

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...