-
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
-
2. Re: 10th business day should show current month data
Sujay Paranjpe Feb 13, 2019 4:32 PM (in response to 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.
-
3. Re: 10th business day should show current month data
Shinichiro MurakamiFeb 13, 2019 4:48 PM (in response to bhargavi chittibomma)
-
8thbusinessday_v10.5.twbx 42.8 KB
-
-
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
-
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
-
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
-
7. Re: 10th business day should show current month data
bhargavi chittibomma Feb 14, 2019 7:43 AM (in response to Andres Garcia) -
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