Product Tour
forums
Use these forums to ask questions and discuss Tableau.
Aggregations by Day
My data set is simple: A timesheet where a user enters hours for activities. They may work on multiple activities per day. e.g.
Emp Hours Activity Date
1 4.5 123 1/1/08
1 3.5 157 1/1/08
2 8.0 143 1/1/08
1 8.0 123 1/2/08
I want to see all employees who are underreporting their hours such that their daily average hours billed for a month is below a threshold e.g. 8 hours.
I'm having a hard time getting the aggregation to work by day. I figured I'd put my date measure in the "Level of Detail" box and use the "M/D/YY" value. Unfortunately, the aggregation still doesn't seem to be taking place at the Day level. The result is that employees such as Emp 1 above will appear to have an average below 8 because they've broken up their activity reporting, even though they've billed 8 hours each day like employee 2.
I've added the attachment to clarify. Each dot represents the employee's average hours billed per day, by month. The reference line is at eight hours. Many employees appear to be underbilling, but this is deceptive -- they are billing multiple smaller chunks of hours per day, and I just can't get the measure to work properly.
| Attachment | Size |
|---|---|
| hours2.png | 30.75 KB |
Subscribe to the comments on "Aggregations by Day"
Comments
The difficulty is that you're effectively trying to aggregate at two different levels -- you want to SUM over each MDY(Date) but then AVG over each Month(Date).
One way to solve the problem is to compute the avg hours worked per day directly, perhaps with this calculated field
SUM([Time Quantity])/COUNTD(DATETRUNC([Date]))
You would then use Month (or year or whatever level you want to average at) as the level of detail.
The DATETRUNC ensures that we're just getting the date part and not any time part that may be present. If you know your data is just dates, you don't need to use DATETRUNC.
Note that that this makes explicit what may have been implicit in your earlier viz -- it's computing the average hours billed per day with a timecard entry. If your data always has an entry with 0 hours for any workday where no time was billed, you avoid this problem.
I just realized I worked out that solution assuming that each employee remained on their own line. If you aggregate by, say, department, the hours per day calculation will fall down --
SUM([Time Quantity]) will increase (roughly by a factor of the number of employees in the department).
COUNTD(DATETRUNC([Date])) will remain constant (there's the same number of days in a month no matter how many employees you have.
If this is a concern, make the formulat like this:
SUM([Time Quantity])/COUNTD( STR( DATETRUNC[Date] ) + ';' + STR([Emp]))
Now, what is being counted by the COUNTD is not days but employee-days, and the calculation will be correct for any aggregation
Dirk, is COUNTD a database specific function? I don't see it in my list of functions. And COUNT isn't giving me the result I want.
Thanks.
COUNTD is specific to the type of data source you are using. It is not available to local file data sources such as Excel, Access, and text files. You can, however, select Data > Extract to create a Tableau Data Extract. When you extract the data, the COUNTD function becomes available.
I think another way is to do some work with the source.
I mean creating pivot table if the source is Excel or proper view if the source is some kind of database. Then one of aggregations (MDY(Date)) will be done in advance.
It will be much faster also.