Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I am trying to apply factors to an existing metric. For example I am trying to divide the 2016 Written Premium by the Actual factor and then multiple the result by the forecast. Here is the way the 2016 Written Premium set analysis is written: sum({<YEAR={$(=Max(YEAR))}>} WP). Any help would be much appreciated.
Try this
Sum(Aggr(Sum({<YEAR={$(=Max(YEAR))}>}WP)/
Sum({<YEAR={$(=Max(YEAR))}>}If(CAL_DATE = Translation_Month_Year, Actual/Forecast)), CAL_DATE))
The performance will be poor because you are using an Island table. May be you can join the two tables on CAL_DATE and Translation_Month_Year fields
May be this
Sum({<YEAR={$(=Max(YEAR))}>} WP/Actual*Forecast)
Hi Sunny, that didn't seem to return the desired result. Here is what I am expecting the calculation to function like.
Is this not what you got with what I provided? Would you be able to share a sample?
Preparing examples for Upload - Reduction and Data Scrambling
Hi Sunny, See attached. The reduce data didn't work (or I was doing it wrong) so I loaded dummy rows. I left the expression you suggested in there so you can see what it was returning versus what I said I expected to see. Thanks.
Try this
Sum(Aggr(Sum({<YEAR={$(=Max(YEAR))}>}WP)/
Sum({<YEAR={$(=Max(YEAR))}>}If(CAL_DATE = Translation_Month_Year, Actual/Forecast)), CAL_DATE))
The performance will be poor because you are using an Island table. May be you can join the two tables on CAL_DATE and Translation_Month_Year fields
Hi Sunny, your formula works, very cool! I'm thinking joining the two tables might be the better way to go if this will be a performance hit. I'll try that way. Thank you for your help