This is a classic utilization problem. I have a post about it here: http://www.datadrivenconsulting.com/2012/10/queues-and-utilization-in-tableau-part-1-queues/. This is based on the excellent help I have received on this forum.
Yours is a little different - I have a question - wouldn't 12/2 have 52 CPU cycles or does the CPU usage end at midnight?
Edit: I guess your results are based on the first table?
Here are a few possibilities attached.
I have used the data from your second table, then duplicated the source three times to show different results with different custom SQL. The first uses the approach on my blog but with no steps created ( the lack of steps makes it a little hard to discern daily usage). The issue with this is that the CPU usage starts and stops at midnight, so days like the 5th actually show no usage for the day (we are using a running sum as part of the chart)
The second SQL statement adds 24 hours to the end date, representing the worst case - that the CPU cycles ran all 24 hours. Now the 5th has a value, but without the steps its still difficult to discern what's going on.
Finally the last SQL has added marks where no CPU cycles are added or taken away, but allow us to create steps. Joe Mako has a neater way of creating steps (http://community.tableau.com/message/135833#135833), but I couldn't get that to work with the utilization setup we have here.
utilization.twbx.zip 26.0 KB
you can create a calculated field for if [start date] != [end date] then [CPUused] else null (not sure if you can have the CPUused in the past vs. now in the same table to display the same CPU being used) end
That won't work in this instance.
Edit: let me explain why. If you had tried your equation, you'd have seen that against the given data it doesn't give the right results. That's because the row level data can only ever represent the dates you have information for. If the CPU cycles start on the 1st and end on the 5th, in the original data format there are no rows for the 2nd, 3rd, or 4th. Therefore Tableau could never know about the CPU cycles on these dates. We need to do one of two things, rearrange the data so we can increment and decrement the CPU cycles (as I have done), or pad the data out so that we have information for every single day (creating time buckets). This is also done with custom SQL.
Regarding your question: Yours is a little different - I have a question - wouldn't 12/2 have 52 CPU cycles or does the CPU usage end at midnight?
No. For the data example below, I would like a data point of 26 for 12/1/2012, 12/2/2012 and 12/3/2012.
jobid startdate enddate cpuused 1 12/1/2012 12/3/2012 26
Thank you for your help!
Then that is what my solution provides I believe.