topic Re: Help with AGGR expression in QlikView

Help with AGGR expression

hopkinsc — Wed, 25 Nov 2020 16:16:04 GMT

I am trying to get the LTA Promo Impact expression to work if an item doesn't have a forecast value.

The Annual Sales and STD Margin expressions have been done but i am struggling to get the LTA expression working.

The LTA expression uses the Annual Sales formula but i can't seem to get the same logic working.

Also the same issue with Price Variance Impact

any help would be great

Re: Help with AGGR expression

sunny_talwar — Tue, 01 May 2018 13:37:37 GMT

Sum([LTA %] seems to be not available

Re: Help with AGGR expression

hopkinsc — Tue, 01 May 2018 13:48:42 GMT

I'm so sorry!! try the attached.

You have just alerted me to a problem with the link to a specific customer! thanks!

Re: Help with AGGR expression

sunny_talwar — Tue, 01 May 2018 13:59:54 GMT

Value show up... what is the issue now?

Re: Help with AGGR expression

hopkinsc — Tue, 01 May 2018 14:14:19 GMT

the top row has no data. thats the one that doesnt have a forecast value and the one that we had to make the changes to the first 2 expressions.

the value should be approx -639.18

Re: Help with AGGR expression

sunny_talwar — Tue, 01 May 2018 14:18:47 GMT

Sorry, I am not sure I understand, what is the expected output for each row and total?

Re: Help with AGGR expression

hopkinsc — Tue, 01 May 2018 14:38:29 GMT

I'll try to explain a bit better..

The top row has no forecast sales, so the standard calculation i use for annual sales doesn't work (the calculation is YTD Sales + (Avg Price*Forecast volume) - so the forecast volume is 0, the 2nd part of the calc doesn't work).

so we changed to only take ytd sales if forecast doesnt exist.

The LTA and Price Variance Impact expressions use the standard annual sales formula, so this needs to be changed to also calculate for items that don't have a forecast. I thought i had done it but its still returning zero. but when i manually calculate the expression it returns -639.18.

manual calc is : 7102.08 * - 0.09 = 639.1872

so the total should be -12,336.64128

Re: Help with AGGR expression

sunny_talwar — Tue, 01 May 2018 16:43:11 GMT

Try this

Sum({<Period>}Aggr(If(IsNull($(vForecastSales)), $(vTotalSalestoLastPeriod)*-sum(TOTAL [LTA %]), $(vLTAPromoImpact)), SubCat, SubCat1, [Item Description]))

LTA % looks like it is 0.09, but it is actually 0.092

Re: Help with AGGR expression

hopkinsc — Wed, 02 May 2018 15:21:23 GMT

Fantastic, thanks very much!