In addition to kumar bharat suggestions, please see the attached workbook (Test Sheets) which might help point you in the right direction, however I think the numbers need validating...
Based on your calc's it also looks like you've found Jonathan Drummey's calc for time duration and summing which is also very helpful, which I also found useful a while back for one of my projects.
To help with the validation part, I was curious about the worksheet title stating that the sum of both locations A & B per ID per week would be the equivalent grand total of 1 week or rather 07:00:00:00 (dd:hh:mm:ss). What I'm seeing in the attached is closer to the sum of 1 week per location per ID per week?
For example, in one week for location AA1 Week of April 15, 2018, it appears there are several days timespan where the duration of seconds is over 11 days in duration, meaning a particular location and particular ID could potentially have well over a 1-week equivalence of time? Further meaning, a grand total of 07:00:00:00 on rows probably wouldn't be achievable each and every time as the possibility would exist for their to be more time duration accumulated than 1-week.
Also, to get to that level of view, required setting a table calculation which essentially sees the 1st week of each unique ID and location as "0", then sums up going forward, restarting at "0" for each ID/Location. So, with a larger data set, that would be the net effect. If that's not the desired result, then you might need Jonathan Drummey's help with not only the time calc but the table calc. He's very amenable to helping out if called upon, but I think he (like me) is going to need a bit more context as to what you're looking to do with the end results, or what's being measured.
Location.twbx 237.2 KB
Sam Elegure had emailed me on this and I have several questions because they make a big difference in the complexity of a solution:
1) Is the first datestamp in a week to be treated as a duration of a) "beginning of week to datestamp" or b) "beginning of day to datestamp"? It's currently implemented as the latter which makes for short weeks when the first datestamp is on the 2nd or later day of the week.
2) Is the last datestamp in a week to be treated as a duration of a) "prior datestamp to current datestamp" or b) "prior datestamp to current datestamp plus datestamp to end of week"? It's currently implemented as the former which means that every week is getting truncated.
3) Does the view have to show the grand total adding up to 7:00:00:00 or is it possible to just do that visually?
4) In your production data how many datestamps for each ID will you be dealing with (and how many IDs)? If the total number of datestamp/ID combinations gets too large you might need to do some preparation outside of Tableau in order to maintain performance?
5) Are you using a live connection or extract?
6) Do you have a data preparation tool or SQL skills available?
Hopefully he got back to you on all of those questions!
I couldn't figure out how Location A and Location B combined would add up to 7:00:00:00 with the data provided as each location seemed to add up to possibly that equivalent per location,
Jonathan Drummey, thanks for creating time, below are the response to your questions.
1. The first datestamp should be treated as a duration of a begining of a week. i.e. If the first datestamp of the week is 06:15:00 at location A. Then it would mean, there is a duration of 6:15 hours in location A
2. The later is the objective. Prior datestamp to current datestamp to end of week.
3. Showing it graphically or in tabular form is welcomed.
4. About 800 IDs with different datestamps.
5. I am using an extract
6. I have sequel as a data prep tool
The date stamps are generated from pings from various mobile units. The purpose is to get correct duration each unit spend in location A or B on a weekly basis
Here's a solution. It follows the logic you described and uses a self-union as described in http://drawingwithnumbers.artisart.org/grand-totals-part-3 in order to make two copies of the data to get around a) Tableau not doing two pass aka visual totals for table calcs and then b) a complex issue of table calculations causing unwanted data densification that makes unfeasible to use a custom grand total and then a set of nested table calculations. Note the use of ATTR() on the Location & GT pill on columns while there's a dimension pill for the field on detail, this is to prevent unwanted data densification.
Let me know if you have any questions!
weekly duration.twbx 654.5 KB
Thank you very much! You just made my week