topic Re: Apply Factors to Metric in QlikView

Apply Factors to Metric

carlcimino — Mon, 10 Jul 2017 19:21:03 GMT

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.

Re: Apply Factors to Metric

sunny_talwar — Mon, 10 Jul 2017 19:36:36 GMT

May be this

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

Re: Apply Factors to Metric

carlcimino — Mon, 10 Jul 2017 20:22:42 GMT

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

Re: Apply Factors to Metric

sunny_talwar — Tue, 11 Jul 2017 08:53:03 GMT

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

Re: Apply Factors to Metric

carlcimino — Mon, 17 Jul 2017 13:33:18 GMT

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.

Re: Apply Factors to Metric

sunny_talwar — Mon, 17 Jul 2017 15:17:33 GMT

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

Re: Apply Factors to Metric

carlcimino — Tue, 18 Jul 2017 15:22:04 GMT

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