1 of 1 people found this helpful
Dan 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.
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
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)
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.
Thanks again for your help. I worked out the following solution:
(2*((DATEPART('week', [Date1]) + 52*DATEDIFF('year',[Date2], [Date1])) - DATEPART('week', [Date2])))
(2*(DATEPART('week', [Date1]) -DATEPART('week', [Date2])))
A fine example of date manipulation. Bet you learned a lot on that one.
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????
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!
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.
WorkingDaysCalculation.twbx 44.5 KB