Include Data from Last Business Day

[acbishop2]

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:

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

[dwforest]

Is it simply changing it to:

Firstworkdate(today()-1,0,$(vHolidays)) , the example in Help infers the second parameter is an offset number of days from the Firstworkdate relative t the first parameter.

[Digvijay_Singh]

Not sure but you can try today(1) to see if it makes the difference -

Today( 0)	Returns the day of the last finished data load.
Today( 1)	When used in a visualization expression, this returns the day of the function call. When used in a data load script, this returns the day when the current data load started.
Today( 2)	Returns the day when the app was opened.

[acbishop2]

Hi David,

With 0 as the second parameter, no date is returned. I have tested it out by having no restrictions on my [Date] field in the script and creating a KPI box with your expression in it. No date is returned.

I also tried inputting it straight into the script and it made all of my visualizations have errors. None of them displayed anything useful.

But perhaps I am misunderstanding. Please let me know if this is the case and you still stand by your answer.

-Aaron

[dwforest · Accepted solution]

Does your date field contain time? if so you may need to try:

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