Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all
I have a problem with the following calculation. Basically I'm trying to create a customized formula where --> if the month is in the past then I need to pick the sales, otherwise I need to pick the forecast.
I want to obtain the aggregation on the quarter in order to have sales (of the past months) + forecast (for present and future month).
I'm trying to use the following formula:
if ( [Month Year] >= Num(Num(year(today())) & Num(Month(Today()), '00')),
sum({<[Month Year] = >}[Forecast Qty]),
sum({<[Month Year] = >}[Sales Qty]))
As you can see it works if I maintain the Month Year level in the table, but when I try to take it out in order to aggregate the data at Quarter Level, it returns all zeros.
Any hint on how to solve this? where am I wrong?
thanks all in advance!
1) Your if-statement would need to be wrapped with sum(aggr( <calculation>, [Month Year])).
2) but i would do this instead.
sum({<[Month Year] = {">=$(=Num(Num(year(today())) & Num(Month(Today()), '00')))"} >}[Forecast Qty])
+
sum({<[Month Year] = {"<$(=Num(Num(year(today())) & Num(Month(Today()), '00')))"} >}[Sales Qty])
3) Or better yet, since your condition is based on today(), flag MonthYear in your data model/load script with a binary flag field and use that in your set analysis.
1) Your if-statement would need to be wrapped with sum(aggr( <calculation>, [Month Year])).
2) but i would do this instead.
sum({<[Month Year] = {">=$(=Num(Num(year(today())) & Num(Month(Today()), '00')))"} >}[Forecast Qty])
+
sum({<[Month Year] = {"<$(=Num(Num(year(today())) & Num(Month(Today()), '00')))"} >}[Sales Qty])
3) Or better yet, since your condition is based on today(), flag MonthYear in your data model/load script with a binary flag field and use that in your set analysis.