9 Replies Latest reply on Feb 21, 2019 10:16 AM by bhargavi chittibomma

# 10th business day should show current month data

Hi All,

I have the below requirement

My revenue data would be calculated end of every month and that would be populated to our datasheets every 8th business day of each month. So If its 8th business day tableau should automatically calculate and show current month data, if not should show previous month data.

The below is the logic i am using to show just previous month data. But don't know how to calcuate the 8th business day current month data logic.

IF DATEDIFF('month',[Revenue Year],TODAY())=1 then [REVN_AMT] END

can any one help me in modifying the logic to show what i need,,

Instant help on this would be appreciated

Thanks

Bhargavi

• ###### 1. Re: 10th business day should show current month data

Hi Bhargavi! hope you're well!

This calc have the rules that you want:

IF DATEPART('weekday',TODAY()) <=6 AND DATEPART('day',TODAY())>=8 THEN

IF DATEDIFF('month',[Revenue Year],TODAY())=0 then //CURRENT_VALUES END

ELSE

IF DATEDIFF('month',[Revenue Year],TODAY())=1 then [REVN_AMT] END

END

hope it helps you!

Andres FG

• ###### 2. Re: 10th business day should show current month data

Calculations around business days is always pain. Business days depend upon the holiday calendar, weekends etc.

We had a similar problem and we solved it by introducing a calendar dimension table. We loaded all the calendar dates for next 20 years with "Business Day" and "Holiday" column.

If first of the month is a public holiday then we set second of the month as "first" business day adn so on...We set the values manually as one time effort and we lookup into this table (or use join) to determine the business day for today and accordingly use it on Tableau.

Its a a bit of had work but it works like a charm!

Best,

Sujay

If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: 10th business day should show current month data

Only considering Weekends, not considering holidays,

Thanks,

Shin

• ###### 4. Re: 10th business day should show current month data

Thanks for the reply sujay. Would you mind to share the logic or elaborate a little bit about what you have said please..

Once again thanks for the response

Thanks

Bhargavi

• ###### 5. Re: 10th business day should show current month data

Hi Garcia,

I  good hope you’re well too. Thanks for the logic shall try it out and let you know if that works

Thanks again

Thanks

Bhargavi

• ###### 6. Re: 10th business day should show current month data

Thanks for the links. Shall try those links And let you know how it goes

Once again thanks for the help

Thanks

Bhargavi

• ###### 7. Re: 10th business day should show current month data

I have tried this logic

IF DATEPART('weekday',TODAY()) <=6 AND DATEPART('day',TODAY())>=8 THEN

IF DATEDIFF('month',[Revenue Year],TODAY())=0 then [REVN_AMT] END

ELSE

IF DATEDIFF('month',[Revenue Year],TODAY())=1 then [REVN_AMT] END

END

I am seeing the result as blank

• ###### 8. Re: 10th business day should show current month data

Hello, that happens because you're only evaluating for today with TODAY() Function. Since you create the calculation on the 14th of February according to your business logic it should only show your current month.

You can test that calculation by replacing TODAY() with a manual date like #01/01/2019# or #12/08/2019#

IF DATEPART('weekday',#12/08/2019#) <=6 AND DATEPART('day',#12/08/2019#)>=8 THEN

IF DATEDIFF('month',[Revenue Year],#12/08/2019#)=0 then [REVN_AMT] END

ELSE

IF DATEDIFF('month',[Revenue Year],#12/08/2019#)=1 then [REVN_AMT] END

END

Regards!

AndresFG

• ###### 9. Re: 10th business day should show current month data

I have created max(rev_date) as a date field and has given this date as a reference date to all of my calculated fields. And it worked like a charm.