Hi,
I am trying to calculate the difference in seconds between ranked timestamps, where result is the number of seconds between timestamp 1 and timestamp 2; ie:
Timestamp Rank DataItem1
6:02:59 PM 742 29
6:02:30 PM 741 22
6:02:08 PM 740 370
5:55:58 PM 739
Can someone help?
With set based logic, you can achieve this by taking 2 identical query definitions and joining this over a sequential field that has an offset of 1.
In your case the rank would be perfect to line up the 2 sets joining them over rank and rank-1. This enables you to perform a straightforward difference calc with the resulting row..
is this DMR/OLAP or relational?
it's relational.
what blom0344 suggested sounds like the way to go
create two query items, join them on rank = rank + 1 and then perform the difference between, what will be, timestamp 1 and timestamp 2
It is not about singular query items, but about joining either 2 query subjects (in the model) or - within the report - using a local join between 2 queries. The reason for me using the model example is re-usability of the solution. It will probably a bit faster too..
so I created the 2 queries and joined them on rank=rank-1. Thing is, I'm somehow missing ranks in the resulting query. I've tried adjusting the cardinalities but that doesn't seem to be the issue. Any ideas?
set auto-aggregation of the queries to 'no' and use 1:1 cardinality. You should expect to lose 1 rank by the join