Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

9 Replies
Anonymous
Not applicable
Author

Try to enclose the whole expresion in another aggr()

Not applicable
Author

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

Anonymous
Not applicable
Author

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

aggr(<your expression>, ProductName)

Not applicable
Author

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.

Anonymous
Not applicable
Author

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?

Not applicable
Author

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

Anonymous
Not applicable
Author

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

Not applicable
Author

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

Anonymous
Not applicable
Author

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