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:
Load * Inline
CHR(39) & Concat([HolidayDate],Chr(39)&','&Chr(39)) & CHR(39) as ConcatHolidays
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.
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.