Set analysis with IF statement

[#1]

Hi all,

I have the following expression:

SUM({$<Cal.Year = {$(=ONLY(Cal.Year))}, Cal.Month = {"<=$(=MAX(Cal.Month))"}>} BUD_REV_CEUs)

which calculates YTD total of BUD_REV_CEUs. This works fine.

I have another dimension "BUD_REV" which specifies the Budget or Revision (eg. Budget, Rev1, Rev2 etc.) and I need to restrict the expression to only calculate where BUD_REV = REV2. I tried the following without success:

SUM({$<Cal.Year = {$(=ONLY(Cal.Year))}, Cal.Month = {"<=$(=MAX(Cal.Month))"}>} IF(BUD_REV = 'REV2', BUD_REV_CEUs))

Please can someone suggest how to make this work?

Many thanks,

APS

[#2 · Accepted solution]

Ah, this seems to work:

SUM({$<Cal.Year = {$(=ONLY(Cal.Year))}, Cal.Month = {"<=$(=MAX(Cal.Month))"}, BUD_REV = {"Bud ET CEUs"}>} BUD_REV_CEUs)

I thought it would probably be an easy one!