Example filter: Next 4 months

You can paste this expression in the filter editor to create the Next 4 months date filter.

// validate: 1 = 1
#$_this.parent.idForExpression# >=
        #queryValue($_this.parent.split.ref + '.dMonth',
                    $_this.parent.split.ref + '.TheDate = ' + $_as_of_date)# 
AND
#$_this.parent.idForExpression# < 
      #queryValue($_this.parent.split.ref + '.dMonth',
                  $_this.parent.split.ref + '.TheDate = ' + 
          #queryValue($_this.parent.split.ref + 'NM_The_Date',
                      $_this.parent.split.ref + '.TheDate = ' + 
              #queryValue($_this.parent.split.ref + 'NM_The_Date',
                          $_this.parent.split.ref + '.TheDate = ' + 
                  #queryValue($_this.parent.split.ref + 'NM_The_Date',
                              $_this.parent.split.ref + '.TheDate = ' + 
                       #queryValue($_this.parent.split.ref + 'NM_The_Date',
                                   $_this.parent.split.ref + '.TheDate = ' + $_as_of_date)
                  )  
              )                  
          )                
      )#
   

Here are the steps that were used to build this expression:

1. Identify the calendar columns to use.

   The expression uses the TheDate, dMonth, and NM_TheDate columns from the sample Gregorian calendar.

   For more information, see Sample calendars.

2. Define the filter lower and upper bounds.

   The filter lower bound is the first day of the month containing the date that is represented by the _as_of_date parameter. The upper bound is the last day of the month that is 3 months after the month containing the _as_of_date date.

   The following table shows the move intervals for the upper bound dates when the _as_of_date date (TheDate) is December 19, 2018.

   TheDate	NM_TheDate	dMonth
   2018-12-19	2019-01-19	2018-12-01
   2019-01-19	2019-02-19
   2019-02-19	2019-03-19
   2019-03-19	2019-04-19
   2019-04-19		2019-04-01

3. Define the move intervals for the lower bound and upper bound.

   The filter timeline consists of move intervals that are based on the columns NM_TheDate and dMonth .

   The move interval for the lower bound timeline is dMonth.

   The upper bound timeline includes the following move intervals:

   • Move interval 1: NM_TheDate
   • Move interval 2: NM_TheDate
   • Move interval 3: NM_TheDate
   • Move interval 4: NM_TheDate
   • Move interval 5: dMonth

   Here is a graphical representation of the timeline when the _as_of_date date is December 19, 2018.

   
4. Map each move interval to one queryValue macro.

   The lower bound expression consists of one queryValue macro, as shown below:

   #$_this.parent.idForExpression# >=
           #queryValue($_this.parent.split.ref + '.dMonth',
                       $_this.parent.split.ref + '.TheDate = ' + $_as_of_date)#
      

   The upper bound expression consists of 5 queryValue macros, nested within each other. Each queryValue maps to one move interval. The earlier move intervals are nested within the later move intervals, as shown below:

   
   #$_this.parent.idForExpression# < 
         #queryValue($_this.parent.split.ref + '.dMonth',
                     $_this.parent.split.ref + '.TheDate = ' + 
             #queryValue($_this.parent.split.ref + 'NM_The_Date',
                         $_this.parent.split.ref + '.TheDate = ' + 
                 #queryValue($_this.parent.split.ref + 'NM_The_Date',
                             $_this.parent.split.ref + '.TheDate = ' + 
                     #queryValue($_this.parent.split.ref + 'NM_The_Date',
                                 $_this.parent.split.ref + '.TheDate = ' + 
                          #queryValue($_this.parent.split.ref + 'NM_The_Date',
                                      $_this.parent.split.ref + '.TheDate = ' + $_as_of_date)
                     )          
                 )               
             )                
         )#
      

   This expression uses the less than (<) sign because the filter includes only dates prior to the upper bound, and not equal to the upper bound itself.