Set analysis aggregation fail

[rm1]

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!

[stevejoyce · Accepted solution]

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.