Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Help with AGGR expression

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

8 Replies
sunny_talwar

Sum([LTA %] seems to be not available

Capture.PNG

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

I'm so sorry!! try the attached.

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

sunny_talwar

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

Capture.PNG

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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

sunny_talwar

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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

sunny_talwar

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Fantastic, thanks very much!