and then use a Data Source filter to delete all the holidays from the data
4-Look back N business days
Some users want to find a value in the several days before the current day –
It can be done easily with a datediff function when all 7 dates are included. But to look back N days excluding weekends is more complex.
The example below is broken into individual formulas that you can combine in your workbook – also it determines the number of calendar days needed – the solution uses actual date values so dimensional filters can be applied without affecting the calculation
First, the Lookback period in business days is set using a parameter (just a simple integer)
To simplify some of them I wanted to use Monday as the first day of the week and used the previous Datepart week formula in:
if [date part weekday]>=1 and [date part weekday]<=7 then
[date part weekday]-1 else 0 end
You may be tempted to use datepart('weekday',[date],'monday') – unfortunately, datepart only supports weekdays for weeks that begin on the default – in my case Sunday
Next, we need the number of 5 day weeks back based on the value of the Business Day Back Parameter – the Min Number of Weeks is:
Int(([Number of day back]+1)/5)
but that is the number of whole weeks and it needs to be adjusted for the position of the date within the week – for that use a function called modulo –
the function returns the remainder of the division of the argument (the parameter value +1) divided by 5
([Number of day back]+1)%5
The total number of weekends then is
{ FIXED [Order Date]:(
if min([jd datepart start monday])=0 then Min([JD Number of weeks ] )
elseif min([jd datepart start monday])<int(Min([jd modulo day of week]))
then Min([JD Number of weeks ])+1 else Min([JD Number of weeks ] )end ) }
The formula used to add in the Saturday/Sunday weekend days to the Business Days Back parameter to determine the number of calendar days to use in the date diff calculation
int(min([Number of day back])) + 2*(min([number of weekend days]))
finally, the calendar date for N Business days in the past can be determined as
{ FIXED [Order Date] :DATE(dateadd('day',-[JD number of dates back],Min([Order Date]))) }
For example, using December 2019 Superstore data looking back 7 business days would return :
Great but real business questions are usually about the growth from the over the period or the total sales looking back over all the dates. To get that we need a start date and a parameter.
First, determine and fix the calendar date based on the start date and the number of business days to look back
{ FIXED : min(if [Enter start date]=[Order Date] then [JD date of back dated] end )}
then the value on that date becomes:
IFNULL({ FIXED :sum(if [JD Fixed back date from param]=
[Order Date] then [Sales] end)} ,0)
The ifnull adjust for dates where there were no sales
IFNULL({ FIXED [Order Date]: sum( if DATETRUNC('day',[Order Date]) =
DATETRUNC('day',[Enter start date]) then [Sales] end )} ,0)
And the percent difference is:
If (sum([JD value at lookback date]))=0 then 1
elseif (sum([jd value on start date parameter value]))=0 then 0
else(sum([jd value on start date parameter value])- sum([JD value at lookback date]))/sum([JD value at lookback date])
end
All the calculations are LOD's and can be used to create a simple summary table
Determining the total sales over the "lookback period" will require another LOD
IFNULL({ FIXED :sum(
if [JD Fixed back date from param]<= [Order Date] and
[Order Date]<=[Enter start date]
then [Sales] end)} ,0)
Lots of LOD's and can be confusing but necessary to avoid table calculations that are based on a position in a table and not an actual date.
There could be many more examples and variations on viewing sales by business day. I encourage you to try some of your own –
The examples here are captured in a workbook that has been posted to my public site at
Jim