topic Re: Set Analysis For Rolling Months in QlikView

Set Analysis For Rolling Months

Anonymous — Wed, 15 May 2013 12:43:32 GMT

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..

Re: Set Analysis For Rolling Months

er_mohit — Wed, 15 May 2013 12:53:19 GMT

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

Re: Set Analysis For Rolling Months

Anonymous — Wed, 15 May 2013 12:58:56 GMT

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..

Re: Set Analysis For Rolling Months

er_mohit — Wed, 15 May 2013 13:11:19 GMT

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

Re: Set Analysis For Rolling Months

Anonymous — Wed, 15 May 2013 13:15:01 GMT

Not working yet

Re: Set Analysis For Rolling Months

jvitantonio — Wed, 15 May 2013 13:34:44 GMT

could you please attach your application?

Re: Set Analysis For Rolling Months

Anonymous — Wed, 15 May 2013 13:39:01 GMT

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)

Re: Set Analysis For Rolling Months

jvitantonio — Wed, 15 May 2013 13:43:33 GMT

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