Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Try to enclose the whole expresion in another aggr()
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...
You added aggr() incorrectly. It must always contain the field(s) you're aggregating by, e.g.:
aggr(<your expression>, ProductName)
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.
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?
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
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
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....
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...