Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am struggling to write the proper expression to evalute at a product level how much of my last years income was retained (defined by having at least $1 in Current Year) and how much was lost. The problem is I need to summarize at a Rollup Product Level so I need aggregation (I think).
Here is my sample data:
Rollup Product Product :Last Year Rev Current Year Revenue
1 a 100 200
1 b 200 0
2 b 300 500
2 c 400 800
3 a 500 0
3 b 600 1100
What I want my object to look like:
Rollup Product Last Year Rev (Lost) Last Year Revenue (Retained)
1 200 100
2 0 700
3 500 600
You can calculate this in the script (recommended):
T1:
LOAD *
,if([Last Year Rev]>[Current Year Revenue], [Last Year Rev]-[Current Year Revenue] ,0) as Lost
,if([Last Year Rev]<=[Current Year Revenue], [Last Year Rev] ,0) as Retained
INLINE [
Rollup Product, Product, Last Year Rev, Current Year Revenue
1, a, 100, 200
1, b, 200, 0
2, b, 300, 500
2, c, 400, 800
3, a, 500, 0
3, b, 600, 1100
];
Or with expressions in a chart with Rollup Product as dimension:
Lost: sum(if([Last Year Rev]>[Current Year Revenue], [Last Year Rev]-[Current Year Revenue]))
Retained: sum(if([Last Year Rev]<=[Current Year Revenue], [Last Year Rev]))
The problem with the expression is the data has to be evaluated at the Product dimension first. If I do it at the Rollup Product dimension then all Revenue would go to the "Retained" column because at a Product Rollup level there is a least $1 in total across multiple products. It would result in the following.:
Rollup Product Last Year Rev (Lost) Last Year Revenue (Retained)
1 0 300
2 0 700
3 0 1100