-
1. Re: Calculating Working Days between events
Shawn Wallwork May 9, 2013 11:32 AM (in response to Dan Cotton)1 of 1 people found this helpfulDan I was with you all the way up until your last sentence. I'm going to ignore it for now, but if this isn't the solution then you'll need to explain what you mean by >0.
If you use the DATEDIFF() function you mention above and then you create another calculated field with this:
DATENAME('weekday',[Order Date] )
and put this on the filter shelf it will let you filter out all the weekends.
Here's a KB article talking about date functions: http://onlinehelp.tableausoftware.com/current/pro/online/en-us/help.htm#functions_functions_date.html
And here's another one talking about date formats (with a cool viz I've attached): http://kb.tableausoftware.com/articles/knowledgebase/supporteddateformats
Let me know if this didn't answer your question.
--Shawn
-
2. Re: Calculating Working Days between events
Dan Cotton May 9, 2013 1:29 PM (in response to Shawn Wallwork)Shawn,
Thanks for the response. The date information is helpful but didn't do what I expected. I attached a sample of the data I am looking at. I am not sure if I exported the data correctly to show what I am trying to accomplish. The goal is to have the blue line calculate the median days to scan based on the number of days between two events excluding weekends (unless we work on a Saturday which was the reference to days with activity >0 in my original post). If I could start with just excluding weekends, that would be great. I applied the weekday filter you suggested and it did not change my visualization.
-
WorkingDays.twbx.zip 21.3 KB
-
-
3. Re: Calculating Working Days between events
Shawn Wallwork May 9, 2013 1:44 PM (in response to Dan Cotton) -
4. Re: Calculating Working Days between events
Dan Cotton May 9, 2013 1:50 PM (in response to Shawn Wallwork)So...
Is there a way to use datediff and add back weekend days? I would like DateDiff('day',5/1/2013,5/9/2013) = 6, not 8. (or some other function)
-
5. Re: Calculating Working Days between events
Shawn Wallwork May 9, 2013 2:18 PM (in response to Dan Cotton)Dan, when you use DATEDIFF() you're using Tableau's 'internal' calendar. Which creates the 8 days instead of the 6 you want. I'm sure there's a way to subtract out the weekends, but why go through those contortions? Your data is set up so that if you counted the [Number Of Records] between two EventDate (DAY) you would get the 6 result you're looking for.
If there were two date fields (one [INDEX DATE] and one for [SCAN DATE] I could show you how to set this up, but the workbook you posted only has 1 date field.
--Shawn
-
6. Re: Calculating Working Days between events
Dan Cotton Jun 11, 2013 12:04 PM (in response to Shawn Wallwork)Shawn,
Thanks again for your help. I worked out the following solution:
([Date1]-[Date2])-
If
(DATEDIFF('year',[Date2], [Date1]))>=1
then
(2*((DATEPART('week', [Date1]) + 52*DATEDIFF('year',[Date2], [Date1])) - DATEPART('week', [Date2])))
else
(2*(DATEPART('week', [Date1]) -DATEPART('week', [Date2])))
End
-
7. Re: Calculating Working Days between events
Shawn Wallwork Jun 11, 2013 12:11 PM (in response to Dan Cotton)A fine example of date manipulation. Bet you learned a lot on that one.
--Shawn
-
8. Re: Calculating Working Days between events
aamir.zamir.0 Jul 7, 2013 11:58 AM (in response to Dan Cotton)Dan I want to exclude working days and count hours , and also need to count hour for working hours.
can you please guide me in that case????
-
9. Re: Calculating Working Days between events
Pedro Ramos Feb 4, 2015 8:06 AM (in response to Dan Cotton)Hi guys.
I have the following problem:
I wanna create a viz with a set of KPI of "yesterday" (day "n-1"),ie, every morning we want to analyse data collected on the previous day.
I created that viz and filtered the "Date" field for "yesterday". Thus far, everything OK. But when we got to monday, we had 0 information, because we only have info on weekdays. (On mondays we analyse data collected on friday, but It was searching for the sunday data).
How can we modify the "internal calendar" in Tableau to only workdays?
Thanks a lot!
Pedro
-
10. Re: Calculating Working Days between events
Jagjit Singh Feb 2, 2016 3:26 AM (in response to Dan Cotton)Hi Dan,
I tried the formula as above but i'm getting negative days. Can you please have a look at my workbook. Also I have the holiday spreadsheet blended and want to exclude holidays when calculating turn around time.
Thanks,
Jag
-
WorkingDaysCalculation.twbx 44.5 KB
-
-
11. Re: Calculating Working Days between events
Benjamin Rubattel Apr 24, 2018 8:00 AM (in response to Dan Cotton)