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

10th business day should show current month data

bhargavi chittibomma Feb 13, 2019 2:43 PM

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

Andres Garcia Feb 13, 2019 5:12 PM (in response to bhargavi chittibomma)

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
Like Show 0 Likes(0)

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

Sujay Paranjpe Feb 13, 2019 4:32 PM (in response to bhargavi chittibomma)

Hi bhargavi chittibomma

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.
Like Show 0 Likes(0)

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

Shinichiro Murakami Feb 13, 2019 4:48 PM (in response to bhargavi chittibomma)
Only considering Weekends, not considering holidays,

Thanks,
Shin

8thbusinessday_v10.5.twbx 42.8 KB
Like Show 0 Likes(0)

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

bhargavi chittibomma Feb 13, 2019 5:17 PM (in response to Sujay Paranjpe)

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
Like Show 0 Likes(0)

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

bhargavi chittibomma Feb 13, 2019 5:29 PM (in response to Andres Garcia)

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
Like Show 1 Likes(1)

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

bhargavi chittibomma Feb 13, 2019 5:30 PM (in response to Shinichiro Murakami)

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

Once again thanks for the help

Thanks
Bhargavi
Like Show 0 Likes(0)

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

bhargavi chittibomma Feb 14, 2019 7:43 AM (in response to Andres Garcia)

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

Can you please help
Like Show 0 Likes(0)

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

Andres Garcia Feb 15, 2019 9:00 PM (in response to bhargavi chittibomma)

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
Like Show 0 Likes(0)

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

bhargavi chittibomma Feb 21, 2019 10:16 AM (in response to Andres Garcia)

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.
Like Show 0 Likes(0)

Actions