Calculate difference between Timestamps with above

[amber2000]

Hi Everyone,

I want to calculate the difference between 2 times based on the field SSCC_OUT.

I have 2 fields with a Time Stamp but the are identical on the same row.

Example:

SSCC_OUT                    StartingTime          EndingTime          Time_Diff

112345030000034095           22:10:27               22:10:27             00:00:00

112345030000034095           22:11:10               22:11:10              00:00:00

Example of what I would like it to be:

SSCC_OUT                    StartingTime          EndingTime          Time_Diff

112345030000034095           22:10:27               22:10:27             00:00:00

112345030000034095           22:11:10               22:11:10              00:00:43  (difference with the above function)

I've attached a qvw with my efforts on the above() function and so far the result stays 00:00:00

Can anybody help me please?

Kind regards,

Monique

[Chanty4u]

hi,

https://community.qlik.com/message/437410#437410

[sunny_talwar]

May be this:

=Time(rangesum(StartingTime - Aggr(Above(StartingTime), SSCC_OUT, StartingTime)), 'hh:mm:ss')

[amber2000]

Sunny,

Sorry that my answer comes so late, I was testing your expression on a whole lot more data then I've send before.

It's there that I see something is wrong.

I'll send a new attach so you can see for yourself.

Every expression you've tried seems to give wrong results as I use it on my original data.

Kind regards,

Monique

[sunny_talwar · Accepted solution]

Not sure if you are using QV 12 or not, but this can work if you are using QV 12

=Time(RangeSum(StartingTime - Aggr(Above(StartingTime), (SSCC_OUT,  (TEXT)), (StartingTime, (NUMERIC)))), 'hh:mm:ss')

[amber2000]

Hi Sunny,

I'm using version 11.20 SR10

I can't see your changes, the functions TEXT and NUMERIC don't seem to be recognized.

Isn't there something else for QV 11

Kind regards,

Monique

[sunny_talwar]

Its a new introduction, included in QV 12. For this to work in version before QV 12, you will need to Sort your date correctly in the load script.

This is the output, I was seeing using the expression I gave you

Although the expression editor shows me the error, I do get the output I am expecting

To read about this new syntax, read here: The sortable Aggr function is finally here!

The problem is that Aggr function sorts by the load order and you can fix the sort order in the script to fix the issue, or you need to have QV12 to fix it in the expression itself

[amber2000]

Dear Sunny,

It's hopeless, it's not working as you can see.

Where in the script can I fix the sorting issue?  In the sort tab of the pivot table I put SSCC_OUT first and StartingTime second.

The sort tab:

[sunny_talwar]

That is not going to help. Fix will have to be made in the script where you will have to use Order By Statement in a resident load to make sure that load order is like this:

Order By SSCC_OUT, StartingTime;

In the pivot table itself is not going to work as far as I know

[sunny_talwar]

Updating to QV12 is not a option for you?