Duration Calculation Problems

[davehutchinson]

Hi all.  I'm having problems calculating the duration between 2 date/times in qlik and I can't for the life of me work out where I'm going wrong.  I've seen a few solutions here, but they don't seem to work, so hopefully someone can help me?

I have 2 fields in my spreadsheet:

"Session started" and "Session ended"

They are both in the format "DD/MM/YYYY HH:MM"

In excel if I calculate =Session ended - Session started 

I get the right answer, however in Qlik the result is always 00:00 which is wrong.

I've tried:

Interval(([Session ended])-([Session started]),'hh:mm:ss') but that just gives me the result of '-' for every line.

I've also tried: SUM([Session ended])-([Session started]) and get the same result.

And lots of variations of these.  

I'm really stumped as to why this isn't working.  Can anyone see what I'm doing wrong? I've tried doing the calculation in the data load editor as well as in the measure field of the visual and get the same result.

i'm stumped, can anyone help?

Thanks

[hic]

Your dates are not interpreted correctly. They are left-aligned - not right aligned. You need to use an interpretation function in the script, e.g.

Timestamp#("Session started", 'MM/DD/YYYY hh:mm') as "Session started"

See more on https://community.qlik.com/t5/Design/Why-don-t-my-dates-work/ba-p/1465849. The blog post is about QlikView, but the same applies to Qlik Sense.

[davehutchinson]

Hiya, what do you mean?  not formatted?  How do I resolve that?

[hic · Accepted solution]

Formatting is only about how the date is displayed. Interpretation is about how it is loaded, and whether the Qlik engine understands that it is a date.

See also https://community.qlik.com/t5/Design/The-Date-Function/ba-p/1463157.

[davehutchinson]

Awesome.  That appears to have calculated some of them but not all, so I think like you said its a loading issue.  Will go through your other page and hopefully that'll sort it!  Cheers

[hic]

When you have fixed the interpretation in the script, you should also think about which aggregation function to use in the measure. Maybe:

Interval(Avg([Session ended])-Avg([Session started]),'hh:mm:ss')

or perhaps better:

Interval(Max([Session ended])-Min([Session started]),'hh:mm:ss')

See more on https://community.qlik.com/t5/Design/Use-Aggregation-Functions/ba-p/1475833.