KB33154: How to create dynamic date filter with n days range from the last day of month based on a day provided as a prompt answer with MicroStrategy Developer

While the user needs to provide a day value as a prompt answer, the actual condition resolved by the prompt answer may need to be last 3 days of the month to which the selected day belongs to. The following steps describe how to create a report with a condition of date range between “Last day of the month -3 days “ and “last day of the month” based on the prompt answer which is provided in the day level.

• Create text value prompt called “Value (Text)_M1” which format "yyyymmdd". Select “Text” prompt as prompt type.
  

• Create an attribute qualification filter called “(LastD-3)_(LastD)”, that should be based off of the Day attribute. Set "Between" as the Operator. Select "Custom" for both values, so that ApplySimple statements can be added, as shown below:
  

•  
   In this example, the ApplySimple statement is based on Microsoft SQL Server database syntax. 
  
  
  (Last day of prompt month –3) day expression :
  

  
  ApplySimple(`DATEADD(day, -3, DATEADD(day, -1, DATEADD(mm, 1, DATEADD(mm, DATEDIFF(mm, 0, convert(datetime, #0)), 0))) )` ,  ? )

  
  
  
   
  
  Last day of prompt month expression :
  

  
  ApplySimple(`DATEADD(day, -1, DATEADD(mm, 1, DATEADD(mm, DATEDIFF(mm, 0, convert(datetime,#0)), 0)))` ,  ?  )

  
  
  
    
  
• Create a report and add the filter “(LastD-3)_(LastD)” to the report filter. After that, insert the date attribute on template, as shown below:
  

•  Run the report. Answer the prompt with a given date. (In this example, "20080101.")
  

•  
   
  Report SQL will be resolved as follows:
  
  
  

  
  select    distinct CONVERT(DATETIME, CONVERT(VARCHAR(10), a11.sell_date, 101))  CustCol_8
  
  from     tbl_B     a11
  
  where   CONVERT(DATETIME, CONVERT(VARCHAR(10), a11.sell_date, 101))
  
  between DATEADD(day, -3 , DATEADD(day, -1, DATEADD(mm, 1, DATEADD(mm, DATEDIFF(mm, 0, convert(datetime,'20080101')), 0))) )
  
  and DATEADD(day, -1, DATEADD(mm, 1, DATEADD(mm, DATEDIFF(mm, 0, convert(datetime, '20080101')), 0)))

  
  
  
  

 
Notice that "20080101" gets converted to month level in this SQL. Therefore, which day is actually input by the user does not make difference when generating this condition, but it always returns last 3 days of the month.