
1. Re: How to calculate hours worked (per month) based on start and stop times
Simon Runc Mar 4, 2016 10:48 AM (in response to James Keuning)hi James,
Is this what you mean?...attached.
I've changed your [ActionsPerHour] calc to
SUM([Number of Records])
/
MIN([TimeWorked])
bear in mind that, and assuming I've understood your requirements!!), this works as the Level of Detail of the Viz (having userID and Action) is such that you can take the MIN (so will just be a singleton value). If you were to remove, say Actions) from the Viz, it would take the MIN([TimeWorked]) from the entire userID so would calculate differently. As an aggregate calculation, it is dependent on the VizLoD...If you wanted to have it calculated (off canvas) like a Row Level calc then you'd need to use a LoD or Table Calc.
Hope this helps, but please post back if not (or it doesn't make sense)

time  SR.twbx 126.8 KB


2. Re: How to calculate hours worked (per month) based on start and stop times
James Keuning Mar 6, 2016 6:32 PM (in response to Simon Runc)I've taken a look at MIN([TimeWorked]) and what I still need to figure out is how to calculate an accurate total of hours worked for a period when there is no date field on a any shelf.
So, in the view I show in this screenshot, I want to pull the Day of Date off and add up the hours for U03 (13+21+19+1) and add up each action for the period.
(It also occurs to me that I should not use MIN because I might have data where a person has two starts and stops on the same day  a person works two short shifts or punches our for lunch.)

3. Re: How to calculate hours worked (per month) based on start and stop times
Simon Runc Mar 7, 2016 1:47 AM (in response to James Keuning)hi James,
Apologies I hadn't looked at the grain of the data!...and hadn't also known your final usecase wouldn't have the same VizLoD (i.e. by TimeInTimeOut) as the example.
So yes a LoD is required (we could use a Table Calc, but LoD is simpler and will work irrelevant of the VizLoD).
So the new formula to get the (13+21+19+1) for U3 would be
[TimeWorked  FIXED LoD SR]
{FIXED [Userid]: SUM({FIXED [Userid],[Time In1],[Time Out1]: MIN(DATEDIFF("hour",[Time In1], [Time Out1]))})}
so the middle FIXED LoD {FIXED [Userid]: SUM({FIXED [Userid],[Time In1],[Time Out1]: MIN(DATEDIFF("hour",[Time In1], [Time Out1]))} gets the hours between each session (by session I means the combination of [Userid] & [Time In1] & [Time Out1]). As this is repeated for each Action, we only want to take a single value (so I've used MIN...but MAX, AVG, ATTR...etc. would work equally well). We then want, for each [Userid] to add these up. This is what the outer part of the FIXED LoD does.
We can then use this new calculated field in the 'ActionPerHour' calculation
[ActionPerHour  SR]
SUM([Number of Records])
/
SUM([TimeWorked  FIXED LoD SR])
In the attached I've left the [Time In1] & [Time Out1] in the VizLoD, so you can see what's going on, but the calculation will work once this is removed, see the 'Using LoD  SR' tab.
Hope I've got it this time, but let me know if not!

time  SR.twbx 152.5 KB


4. Re: How to calculate hours worked (per month) based on start and stop times
James Keuning Mar 7, 2016 10:57 AM (in response to Simon Runc)Wow, that's it. Thanks a bunch. You enabled me to move on from hours of failing.
My LOFfu is superweak and I had no idea that we could use multiple fields when defining the level.

5. Re: How to calculate hours worked (per month) based on start and stop times
James Keuning Mar 7, 2016 11:37 AM (in response to Simon Runc)One more question  can we make the LOD calculation respond to the date filter?
EDIT: I think I solved this by changing the date filter to a Context filter.

6. Re: How to calculate hours worked (per month) based on start and stop times
Simon Runc Mar 7, 2016 12:32 PM (in response to James Keuning)...glad it did the trick!
For getting going with LoDs...I'd start with this great TTD Video
TDT: LoD of the Rings  May 21, 2015
and then look at these 15 great examples on different ways to use them
https://www.tableau.com/about/blog/LODexpressions?signin=ef097e747837b26390ce4b8bb61f15ab
Yes FIXED are calculated at the same point in the pipeline as calculated sets, so the only way to make them 'filter responsive' is to make the filter 'in context' (if you are interested in the geekier side of tableau, and it's order of filter operations check out this blog...Vizible Difference: Evolution of the Order of Operations Diagram which explains why this is the case). There is a performance cost to using context filters (unless they are filtering out 80%+ of the data, when there's a performance boost!...apparently), but unless your data is very complicated and/or 10M+ rows, it'll be fast enough. You can use INCLUDE/EXCLUDE LoDs, which are filter responsive (and more efficient!), but they are (in part) dependent on your VizLoD, so your exact solution would be hard for me to guess at (unless I know the final use VizLoD)...they are also more complicated (as you need to think about the VizLoD). The 2 vids above will explain how they work, far better than I can!!