Help with AGGR expression

[hopkinsc]

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

[sunny_talwar]

Sum([LTA %] seems to be not available

[hopkinsc]

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?

[hopkinsc]

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]

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 · Accepted solution]

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]

Fantastic, thanks very much!