Apply Factors to Metric

[carlcimino]

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.

[sunny_talwar]

May be this

Sum({<YEAR={$(=Max(YEAR))}>} WP/Actual*Forecast)

[carlcimino]

Hi Sunny, that didn't seem to return the desired result.  Here is what I am expecting the calculation to function like.

[sunny_talwar]

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

[carlcimino]

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.

[sunny_talwar · Accepted solution]

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

[carlcimino]

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