topic How best to decrement for rolling month? in QlikView

How best to decrement for rolling month?

Tanalex — Fri, 01 Sep 2017 19:08:24 GMT

I have been tasked with displaying data based on a loose, rolling "quarter", not a traditional calendar quarter unfortunately.

See the example below. Every month, one month will roll from one group to another. Trying to figure out the most efficient way to tag each month with its' respective "quarter", in the MasterCalendar load script, to be leveraged in the UI. I thought about using =MonthName(addmonths(today(),-0)) and then incrementing accordingly. Am not sure if RowNo, RecNo, or another function would be more efficient.

Thank you in advance for any and all constructive feedback.

Re: How best to decrement for rolling month?

cheenu_janakira — Fri, 01 Sep 2017 20:36:04 GMT

Hi Michael,

I'm not too sure what you are trying to do, but from what I can gather, I quite often use the "autonumber()" function and double barrel (duplicate) fields for calendar groupings. I then reference the autonumber field in set analysis, as it is easier to use than a string field. Obviously, the calendar table build in the script must be in chronological order. In order for the set analysis expression to work, based on user selection on the related "text" field, the set analysis expression must have "[field user for selection]=".

Check the attached app to see if this makes sense. Click around on the month selection box to see if this is the kind of "rolling quarters" you are looking for.

Kr,

Cheenu

Re: How best to decrement for rolling month?

effinty2112 — Fri, 01 Sep 2017 21:08:51 GMT

Hi Michael,

Use this expression in your master calendar script to assign the quarter number you need to each date:

Div(3+Mod(Month(Date)-Month(Today())+12,12),3)as Qtr

Regards

Andrew

Re: How best to decrement for rolling month?

Tanalex — Thu, 19 Oct 2017 17:56:36 GMT

Thank you for that. It took a bit, as I have not used those functions, to wrap my head around it.

Re: How best to decrement for rolling month?

Tanalex — Fri, 09 Mar 2018 21:35:49 GMT

For anyone who is curious, I ended up solving my original dilemma through nested-ifs.

IF( Date(Floor(tmpDate)) <= MonthsEnd(1,Today(),-1) AND Date(Floor(tmpDate)) >= MonthsStart(1,Today(),-3)

,Date(MonthsEnd(1,Today(),-1),'MMMYYYY-') & Date(MonthsStart(1,Today(),-3),'MMMYYYY')

IF( Date(Floor(tmpDate)) <= MonthsEnd(1,Today(),-4) AND Date(Floor(tmpDate)) >= MonthsStart(1,Today(),-6)

,Date(MonthsEnd(1,Today(),-4),'MMMYYYY-') & Date(MonthsStart(1,Today(),-6),'MMMYYYY')

IF( Date(Floor(tmpDate)) <= MonthsEnd(1,Today(),-7) AND Date(Floor(tmpDate)) >= MonthsStart(1,Today(),-9)

,Date(MonthsEnd(1,Today(),-7),'MMMYYYY-') & Date(MonthsStart(1,Today(),-9),'MMMYYYY')

IF( Date(Floor(tmpDate)) <= MonthsEnd(1,Today(),-10) AND Date(Floor(tmpDate)) >= MonthsStart(1,Today(),-12)

,Date(MonthsEnd(1,Today(),-10),'MMMYYYY-') & Date(MonthsStart(1,Today(),-12),'MMMYYYY')

,'')))) AS [Month3Roll3]