Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted
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
Highlighted
Creator
Creator

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

Highlighted
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

Highlighted
Creator
Creator

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

Highlighted
Creator
Creator

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]