I think you can accomplish your results by creating a weekday calculated field.
Calculation 1: Weekday ( This calculation will return the weekday of each date)
Case DATEPART('weekday',[Order Date])
When 1 Then 'Sunday'
When 2 Then 'Monday'
When 3 Then 'Tuesday'
When 4 Then 'Wednesday'
When 5 Then 'Thursday'
When 6 Then 'Friday'
When 7 Then 'Saturday'
Calculation 2: Avg no of Calls
Sum([Number of Records])/Countd([Weekday])
Hope this will help you
I saw your question this morning and was curious as to how to solve the question: How many Sundays are there between two dates?
Just to make sure I understand your problem, I created a few random rows of data for the months of December 2012 and January 2013 with just one column for the timestamp. Given your example above, each row would be a call. Below you can see the number of records for each day. Some days have multiple calls, some have none. (You can right-click Date to create the various dimensions. The calendars on the right were just cut and pasted into the image.)
There are 5 Sundays in December, but only 4 have data. January has 4 Sundays; 3 with data. COUNTD(DATE(Date)) returns 4 and 3, respectively. You want 9, but are getting 7.
While there are techniques to have Tableau fill in missing members (rows) of a domain (dimension) by using Show Missing Values, it's not going to help here and I don't think in general it'll work in this situation. And at any rate, it's easy to use some math.
Dates in Tableau are just floating point values starting at 0 for 1900-01-01 and adding 1 for each full day: 1900-01-02==2, ... up to 41,562 for TODAY(). If you divide the dates by 7, you get the number of weeks, and now you're getting close to counting the number of Sundays, ... .
The basic strategy is to subtract the start date from the end date and convert the days to weeks. There are a couple of tricks and general points to be aware of.
1. Tableau weeks start on Sunday (weekday == 1), but numerical weeks start on Monday (date % 7 == 0).
2. If the weekday of the start date is the same as the day you want to count, you need to add 7 to the end date:
Example 1: Count Sundays between Sunday, 2012-12-02 and 2012-12-02.
In this case the count should be 1.
Date Num Week Num (Date Num / 7) Start Date Sunday 2012-12-02 41,243 5,891.86 (5/6ths) End Date Sunday 2012-12-02 41,243 5,891.86 End Date + 7 Sunday 2012-12-09 41,250 5,892.86
Subtracting these two weeks gives us 0. So we need to add 7 to the end date to make it 5892.96.
Keeping the weekday of the start date as Sunday, you'd need to add different amounts to the end date for the math to work:
Weekday of start date Weekday to count Adder Sunday Sunday 7 Sunday Monday 6 Sunday Tuesday 5 Sunday Wednesday 4 Sunday Thursday 3 Sunday Friday 2 Sunday Saturday 1
4. As you change the weekday the start day, you also need to change the adder:
Weekday of start date Weekday to count Adder Sunday Sunday 7 Monday Sunday 1 Tuesday Sunday 2 Wednesday Sunday 3 Thursday Sunday 4 Friday Sunday 5 Saturday Sunday 6
Example 2: Count Sundays between Monday, 2012-12-03 and Monday, 2012-12-03.
In this case the count should be 0.
Date Num Week Num (Date Num / 7) Start Date Monday 2012-12-03 41,244 5,892 End Date Monday 2012-12-03 41,244 5,892 End Date + 7 Monday 2012-12-10 41,250 5,893 End Date + 1 Tuesday 2012-12-09 41,250 5,892.14
The basic formula for the adder to the end date: DoW Adder =
DATEPART('weekday', [Start date] - [Day of week to count])
Day of week to count is a parameter:
Now you can use DoW Adder in Count of Days =
INT( (([End date] + [DoW Adder]) - [Start date]) / 7 )
Again, End date and Start date are parameters. You can derive these from the view using DATE(WINDOW_MIN(MIN(Date))) and DATE(WINDOW_MAX(MAX(Date))), respectively. There is a problem with this approach, however. Tableau is finding the min / max dates where there is data. In the above example above, January 30 and 31 don't have data, so DATE(WINDOW_MAX(MAX(Date))) == Jan 29. (The DATE() wrapper function truncates the time.)
Attached is the workbook for the above examples.
CountOfWeekday.twbx.zip 108.7 KB
Wow, thank you both for your replies; I need to read those over again to fully digest what you're doing. The first solution proposed was the same solution I implemented essentially, however it still only counts the days that have data.
I still have to read over the second solution to get what you mean, but to make the data plot correctly I joined data from the datasource to a CSV file containing every hour for the next two years to cover all of the hours/days that don't have data. Basically, I do a left-join against that date source to get all the dates and I want it to include the hours that don't join to anything in the graph. If I count the number of days between two dates with that generated CSV file, it shows the correct number of Sundays, Mondays, etc. I just want it to appear in the graph. Here is the SQL generated by Tableau for the left joins:
SELECT [DateGenerated#csv].[DateTime] AS [DateTime],
[Call Log_test#csv].[CALL ID] AS [CALL ID],
[Call Log_test#csv].[TIMESTAMP] AS [TIMESTAMP],
[Call Log_test#csv].[TimeStamp Hour-Only] AS [TimeStamp Hour-Only],
[ActiveAgents#csv].[Date] AS [ActiveAgents#csv_Date],
[ActiveAgents#csv].[Number of Agents] AS [ActiveAgents#csv_Number of Agents]
FROM ( [DateGenerated#csv]
LEFT JOIN [Call Log_test#csv] ON [DateGenerated#csv].[DateTime] = [Call Log_test#csv].[TimeStamp Hour-Only] )
LEFT JOIN [ActiveAgents#csv] ON [DateGenerated#csv].[DateTime] = [ActiveAgents#csv].[Date]
I want to do a COALESCE (like in a database) on the "Call ID" column so it always returns a value and is always counted. Make sense?
After carefully reading your explanation I was able to get the chart to process correctly and calculate an average based on the total number of weekdays between the start and end dates. Thank you both very much.