I am not quite sure understand what you are ultimately looking for, but is a calc field like:
MIN([Added DateTime])-LOOKUP(MIN([Added DateTime]),-1)
what you need to get closer?
I modified one of your sheets to make use of this calc field.
Depending on what your ultimate goal is and how you want to look at these numbers in aggregate, the calculation and partitioning would be different.
time_interval_edit.twbx 72.0 KB
I've done you a couple of views demonstrating the sorts of things you can do, Mel. A few comments.
1) I've assumed that the timestamps represent the completion times for actions, so the duration is worked out as the time from completing the previous action, using LOOKUP([timestamp], -1). If the timestamps are actually start times you need to look up the next start time, using LOOKUP([timestamp], 1).
2) I've assumed that there are no pauses between boxes - so the duration of the first action on box 2 is found by subtracting off the timestamp when the last action of box 1 finished.
3) There is no way to calculate the duration of the very first action (you only know the end time). The way I've done the higher level aggregates also doesn't work out the duration for the first box (or whatever), since you don't the end time for the previous. You could get tricky and work out the time for the first box except for the first action for that box - but as it still wouldn't be accurate I haven't bothered.
4) Duration is expressed in fractional days - that's what you get if you subtract two datetimes and that's what you need to make a Gantt chart like this work.
5) The reason for the field MinusDuration is that a Gantt chart expects a start time and a duration. As we have an end time and a duration, we need to make the duration negative to show the bar in the correct place (i.e. to the left of the end time).
6) [DurationSeconds] just divides the duration in fractional days by the number of seconds in a day (86400) to make the tooltips more useful.
7) I turned on mark borders to highlight where there are multiple consecutive copies of the same action for one box.
Let me know if you have any questions. I use this sort of representation for time-series data all the time - so I may be skipping over something that seems obvious to me but wasn't so obvious the first time round.
time_interval_rl.twbx 89.0 KB
Many thanks for this - it was absolutely spot on. Sorry that it's taken me so long to come back and thank you (as always!) for your rapier insight into the elegant solution to the problem. Right! Time to put another question up there ...
Is there a way a caluclated field can have the following manipulation
Volume = (xyz*(2011Q3) + abc*(2009Q4))/integer
where 2011Q3 = Measure xyz in the year 2011 and quarter Q3
2009Q4 = Measure abc in the year 2009 and quarter Q4
You should start a new thread - this isn't in any way related to Mel's original question, I don't think.
When you do, it would be helpful if you could provide a little context for your question. The short answer is "yes, lots of ways". With a bit more context people will be able to guide you towards the most appropriate option.
any move on this?