Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Tanalex
Creator II
Creator II

How best to decrement for rolling month?

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.

CalendarGrouping.png

1 Solution

Accepted Solutions
effinty2112
Master
Master

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

View solution in original post

4 Replies
cheenu_janakira
Creator III
Creator III

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

effinty2112
Master
Master

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

Tanalex
Creator II
Creator II
Author

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

Tanalex
Creator II
Creator II
Author

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]