If these are not DateTime fields, then I would make them so and perform a DateDiff function. DATEDIFF('day',[subject start],[subject end]). This should give you the decimal part of the day between the start and end times.
You can get the idle time with
WINDOW_SUM( if last() = 0
if DATEDIFF("minute", MIN( [Subject End] ),MIN( DATEADD('hour',2, DATETRUNC('day',[Subject End])) ) ) > 0
then DATEDIFF("minute", MIN( [Subject End] ), MIN( DATEADD('hour',2, DATETRUNC('day',[Subject End])) ) )
if DATEDIFF("minute",MIN( [Subject End] ), LOOKUP( MIN( [Subject Start] ),1 ) ) > 0
then DATEDIFF("minute",MIN( [Subject End] ), LOOKUP( MIN( [Subject Start] ),1 ) )
check the computing
So basically, for the last row (last() = 0) you do a datediff between the Subject End and 2AM (datetrunc Subject End at the day and add 2 hours).
For the other rows do a datediff between the Subject End and the Subject Start of the next row (loockup with index 1).
Make sure to Sum only the positive difference.
For the second question, the Time dimension would need to have a row with a value at 2am if you want to display the Time as a discrete value.
If you are willing to change your view a bit (sheet Example(7)) , you can put Hour(Time) as a continuous axis on the column. You can set the tick marks of the axis at the Hour, and format the Date with HH:mm.
Next you create a calculation DATEADD('hour',2, DATETRUNC('day',[Subject End])) and you bring it on the Detail shelf. From there you create an invisible reference line using this calculation. This will add the 02:00 hour on the axis.
Example_270818(mc).twbx 45.9 KB
Thanks for your quick answer. No way I could have thought of that!
It looks great, but I have more than one classroom. If you check the filter, you can also choose classroom "Class1" , that it has different start and ending time.
Do you think is possible to modify your idle time to multiple start and ending times?
Thanks in advance!!!
Thanks for your email. Unfortunately isn't that simple, because I want to calculate the difference between start and end of different rows, not the same row.
The goal will be to restart the calculation on every Classroom Date instead of every Classroom code.
I made some modification on the idleTime calculation to take into account the different class hours range.
I am not sure it covers all the different cases but at least it gives you an idea on how to apply the logic for this type of problem.
Pay attention , idleTime is now a nested calculation composed of two calculations and you have to set the computing in both.
Also , since you may want to display more than one classroom, with dates spanning on multiple days, your Hour of Time axis will spread on more than one day. You may want to go with Example(8) where the Time(hour) is independant of the Date.
I will be out of the office for the next two weeks, so I might not be able to get back to you for a while if you have other questions.
Example_270818(mc2).twbx 53.3 KB
Once again thanks for your thorough reply.
It works great until I choose Class2. As you can see from the example below:
Idle time should be around 225 mins. (6pm to 615pm) + (1027pm to 2am).
Nevertheless, I appreciate you Idle time rational, It gave me some ideas to apply in other issues.
At the end, I decided to find a workaround in excel, and then upload the Idle time as a column.
Thanks again, and I hope you had enjoyed your holidays!