1 Reply Latest reply on Jul 30, 2013 1:38 PM by Mark Holtz

    Relative Date - YTD and MTD less one day

    Jerry Ward

      So we are trying to get a comparison of 2012 versus 2013 YTD and MTD.  Our data does not have results for the day of it only has data up until yesterday.

       

      I can get the correct results if I use the relative date filter and anchor to yesterdays date for both MTD and YTD.

       

      However I need to build a filter that is dynamic because I want the results to be auto emailed to me.

       

      I have built out two filters to filter out 2012 and 2013.

       

      This filter works for MTD for 2012 data:

       

      IF YEAR([StatDate]) = 2012

      AND MONTH([StatDate]) <= Month(TODAY())

      AND DAY([StatDate]) <= Day(dateadd('day',-1,Today()))

      THEN [Room Revenue] ELSE NULL END

       

      If I use this filter for 2012: IF YEAR([StatDate]) = 2012 THEN  [Room Revenue] ELSE NULL END...... I get data from 2012 but it will give me data up until todays date.  However I dont have 2013 data up untils todays date so it gives off an incorrect percent change.  I dont know why it still gives up until todays date even though I have the Relative Date filter in place below subtracting a day from the StatDate. 

       

      For 2013 I have this Calculated field this I use a relative date filter:

       

      2013:  IF YEAR([StatDate]) = 2013 THEN [Room Revenue] ELSE NULL END

       

      Relative Date Filter:  dateadd('year',datediff('year',[StatDate], dateadd('day',-1,TODAY())),[StatDate])

       

      What is the best way to build a filter for 2012 and 2013 so that it always takes todays date, less one day and give s the results for YTD and MTD???

       

      Thanks,

       

      Jerry