How best to decrement for rolling month?

[Tanalex]

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.

[cheenu_janakira]

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 · Accepted solution]

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]

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

[Tanalex]

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]