Set Analysis For Rolling Months

[Anonymous]

Hi All,

I am facing challange to change a set analysis expression...

PFA png image with sample data

I have following fields with me------

1.) ReportMonth = OL_ReportMonth_FY

2.) ReportMonth = Month(OL_ReportMonth)

3.) OL_Quarter

4.) ((Year("OL_ReportMonth")-1)*12 + Month("OL_ReportMonth")) as OL_ReportDateID

5.) ((Year("OL_Date")-1)*12 + Month("OL_Date")) as OL_DateID

6.) ='Year(ReportMonth) : '&Year(ReportMonth)

My current expression is : Sum({$<Flag={'7'},OL_DateID={">=$(=Max(OL_ReportDateID))"}>}Amount/1000000)

If my OL_ReportDateID is 24147 then I want to select OL_DateID with 24147 +12 (Rolling 12 months). My selections will always include a FY and ReportMonth

Kindly help me with Rolling Months expression without the current selection impact.

Thanks in Advance..

[er_mohit]

Hii

sum({<Flag={'7'},OL_DateID={">$(=Date(Addmonths(Max(OL_DateID),-12),'MMM-YYYY'))<=$(=DAte(Addmonth(Max(OL_DateID),0),'MMM-YYYY'))"}>}Amount/1000000)

Take OL_DateID in dimension

Hope it helps

[Anonymous]

Thanks Mohit.

But still the result is Zero.

If OL_ReportDateID is 24147 then I want resulting values to sum up for OL_DateID with 24147, 24148, 24149, ... till 24158..

[er_mohit]

sum({<Flag={'7'},OL_ReportDateID={24147},OL_DateID={">$(=Date(Addmonths(Max(OL_DateID),-12)))<=$(=DAte(Addmonth(Max(OL_DateID),0)))"}>}Amount/1000000)

I remove the format.

Try to use directly may be  try to check with selection

[Anonymous]

Not working yet

[jvitantonio]

could you please attach your application?

[Anonymous]

Application is 200 MB .. Wont be able to share...

I am trying to use this expression... But sows error in underlined part .. Or if you could assist with some other expression

=Sum({$<Flag={'7'},OL_ReportDateID = {“>=$(=Max(OL_DateID) – 11) <= $(=Max(OL_DateID))”} >} Amount/1000000)

[jvitantonio]

Please reduce the data (File --> Reduce Data) and share the application. It's way easier for us to help you.