Calculating seconds between ranked time fields

[ccbarbeau]

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?

[blom0344]

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..

[adik]

is this DMR/OLAP or relational?

[ccbarbeau]

it's relational.

[adik]

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

[blom0344]

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..

[ccbarbeau]

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?

[blom0344]

set auto-aggregation of the queries to 'no' and use  1:1 cardinality. You should expect to lose 1 rank by the join