Forum Discussion
Time Difference Between two resources
Hi Kumar,
There is two preconditions that we evalute the time spent in one session and the data has orders. Have look at the picture. Let's take session 1 and 2 as example. Time Spent = [the next start time] - [the end time]. The "Time Spent" from session 1 to 2 will be empty. (row 5)
In your latest post, you order the table by "resourceid", so it looks strange. Please reorder it as the orginal one (like below). It's clear there is three moves in session 1 and there will be three "time spent".
I split one formula into two to get high performance.
Best Regards!
Dale
I did not sort it this time, but its still working the same way.
Look at the formula that I have applied, I hope it's not incorrect.
Hi Kumar,
You can see the index is disordered. That's why the result is wrong. If we don't use an index, the performance will be terrible due to we have to filter and compare. Let's add a useful index.
1. Open "Query Editor", select "Session ID", sort a->z;
2. Click to open "Advanced Editor", add ", {"Start Time", Order.Ascending}" as the picture showed;
3. Click "Done", we will see an arrow in "session ID" and an arrow in "Start Time";
4. Add index now.
Why we do this? There is an order along time when people move from one resource to another in one session. If this is true, it would work. We can use (index + 1) to get the next "Start Time" without comparing many "Start Time" to get the most recent "Start Time", which is consuming performance.
Best Regards!
Dale