1 2 Previous Next 16 Replies Latest reply on Aug 22, 2018 3:18 PM by Okechukwu Ossai

# Previous three business days calculation

Hi,

Currently I am trying to write a calculation that works with my Data selection parameter, in the parameter the user will select one date and it will show the previous three business days of that particular date.

In the date dimension I am using there is a field called Business_Day which is eather Y or N (yes, no), I am trying to combine this with DATENAME(weekday). Currently I have this:

IF DATENAME('weekday', [Date selection])= 'Monday' AND [business_day] = 'N'

THEN [Date] = DATEADD('weekday', -3, [Date selection])

OR [Date] = DATEADD('weekday', -4, [Date selection])

OR [Date] = DATEADD('weekday', -5, [Date selection])

ELSEIF DATENAME('weekday', [Date selection])= 'Monday' AND [business_day] = 'Y'

THEN [Date] = [Date selection]

OR [Date] = DATEADD('weekday', -3, [Date selection])

OR [Date] = DATEADD('weekday', -4, [Date selection])

ELSEIF DATENAME('weekday', [Date selection])!= 'Monday'

THEN [Date] = [Date selection]

OR [Date] = DATEADD('weekday', -1, [Date selection])

OR [Date] = DATEADD('weekday', -2, [Date selection])

END

This kind of works, except I want to check everyday on Business_Day = Y so I have to expand this calculation, but I cant seem to figure out how, is it possible to somehow get the DATENAME of the DATEADD, -1, -2, -3 ETC? For example if I select Easter monday (2-4-2018) it currently shows me 30-3-2018 and 29-3-2018. But 30-3-2018 is Business_Day = N (good friday), so in this perfect example I want to show 29-3, 28-3, 27-3.

Or is it possible to get this in another way, I saw some suggestions for LoD expressions in other topics, which might make it easier.

• ###### 1. Re: Previous three business days calculation

Hi Raoul,

Can you please provide sample data or twbx file. It will help us to provide you solution in faster manner.

Regards,

Anupam

• ###### 2. Re: Previous three business days calculation

Hi Raoul,

The attached workbook uses the business days to calculate how many days previous should be included and only includes days marked as business days in the last relevant period. You should be able to use the logic in your workbook.

There might be some changes that you need to make due to the use of Fixed depending on how your data is structured, if this doesn't work then an example workbook would help to work it out.

• ###### 3. Re: Previous three business days calculation

Hi Anupam & Chris,

I have created a workbook based on the superstore, see attached file.

In sheet 1 is my calculation. In sheet 3 I tried chris his calculation.

Just to be clear: I specify a date in date selection say 27th of december, I want to see 27th, 22nd and 21st, nothing more than that (23-26 are not business days). When I would select 22nd, I want to see 22/21/20 (so its today -2 days).

• ###### 4. Re: Previous three business days calculation

Anyone?

• ###### 5. Re: Previous three business days calculation

can you please help us out in this solution provided by Chris works partially Correct but it gives issue for long weekends. Please find the attached twbx for more details.

Some more loops need to be added in the above calculation

I am sure you can help us in this, Thanks in advance!

Regards,

Anupam

• ###### 6. Re: Previous three business days calculation

Hi Anupam,

It will be difficult to get the correct result using DateDiff or other date calculations. You will need to specify several conditions and loops which may end up breaking down when you least expect it or when holiday patterns change especially during Easter when the holiday can fall within March or April.

My recommended solution is to use sets. It seems you are only interested in the last 3 days, so I have hardcoded "3" into the set creation logic. However, you can easily replace this with a parameter if you want to give the user the option to select Last N  days. From Raoul's original question, if 27/12/2017 is selected, he wants to display 22/12/2017, 21/12/2017 and 20/12/2017. If you will rather include the user specified date, then change "<" to "<=" in the set formula.

Step 1: Create a set called [Last 3 Business Days]

Right click on [Date] field and select "Create" and then "Set". Format the dialog box as shown below.

Step 2: Add [Last 3 Business Days] to the filter shelf. Right click and select 'Show In/Out of Set'. Then select "In"

Hope this helps.

Ossai

3 of 3 people found this helpful
• ###### 7. Re: Previous three business days calculation

Hey  Thank You!

Its working as expected.

Regards

Anupam

• ###### 8. Re: Previous three business days calculation

You're welcome.

Check my update. In Raoul's original question, Last 3 days does not include the user specified date. In that case use "<" in the set formula instead of "<=". However, if you want to start counting from the user defined date then use "<=" instead.

Ossai

• ###### 9. Re: Previous three business days calculation

Hi

We can use this set in details as well as in color and it will give us desired output.

Please find the attached .twbx for the same

Regards,

Anupam

2 of 2 people found this helpful
• ###### 10. Re: Previous three business days calculation

Thank you, that did the trick!

Only one kind of strange thing was happening when I put the formula in the set I get the error: the field set 1 has an invalid filter.

So I have created a calculated field with logic in it instead and use that to filter top 3, that does work!

• ###### 11. Re: Previous three business days calculation

Just make a calculated field of the formula and use it in Set definition

Regards,

Anupam

On Wed 16 May, 2018, 7:32 PM raoul.tller, <tableaucommunity@tableau.com>

• ###### 12. Re: Previous three business days calculation

Is what I did, see attached picture.

• ###### 13. Re: Previous three business days calculation

Hi Raoul,

From your attached picture, it seems you are creating the set using 'By field' option. You need to use 'By formula' option instead. You can read through my solution again and just follow the instructions.

Hope this helps.

Ossai

• ###### 14. Re: Previous three business days calculation

Hi,

We tried to create the set using by formula option in 10.5 version but it was giving an error set has an invalid filter. Hence Raoul used by field option and it worked.

Regards,

Anupam

1 2 Previous Next