4 Replies Latest reply: Feb 20, 2018 7:19 PM by David Forest RSS

    Include Data from Last Business Day

    Aaron Bishop

      Hello All,

       

      I have a conundrum. I have a report that has been displaying data up to the previous business day. Within the calculations for my graph, I have just been using the firstworkdate() function, and so far, so good. However, yesterday was a holiday, and today is Tuesday, so I need it to display numbers as of last Friday. For motives of flexibility, I also want to be able to edit the view from the load script to be able to see things from today, the last business day (networkdate(today()-1,1,$(vHolidays))), or two business days ago (firstworkdate(today()-2,1,$(vHolidays))). Here's how I have it set up:

       

      1. Holidays are defined in the script:

       

      HolidaysInline:

      Load * Inline

      [

        HolidayDate

        1/1/2018

        1/15/2018

        2/19/2018

        5/28/2018

        7/4/2018

        9/3/2018

        11/22/2018

        11/23/2018

        12/25/2018

      ];

       

       

      ConcatHolidays:

      Load

        CHR(39) & Concat([HolidayDate],Chr(39)&','&Chr(39)) & CHR(39) as ConcatHolidays

       

      Resident HolidaysInline;

       

      Drop Table HolidaysInline;

       

       

      Let vHolidays = Peek('ConcatHolidays',0,'ConcatHolidays');

       

      Drop Table ConcatHolidays;

       

       

      2. Within the load script, I limit my date file to include only the data up to a certain point:

       

      If(Date(Date)<=Firstworkdate(today()-1,1,$(vHolidays)) AND Year(Date)>=2017,Date) AS Date

       

      In this case (because today is Tuesday and Monday was a Holiday), all data should be current as of Friday.

       

       

      This all seems like it would work just fine; however, with the above setup, data from Friday is not shown. The following graph displays number of leads for last week:

       

      Leads.JPG

      There should be 36 on Friday, 2/16/2018, but I can't get it to show up without changing the date limitations in the script and messing with the rest of the data.

       

      Any ideas why this would be happening?

       

      The chart dimension is: [Date.autoCalendar.Date]

      The chart measure is: Sum([Total Leads])

       

      I appreciate the help.

       

      Aaron Bishop