topic Re: Difference between two timestamps in minutes? in QlikView

Difference between two timestamps in minutes?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi guys,

Please help me get the duration between two timestamps in minutes. I tried the expression only({<[Duration Type]={'Sustained'},[Major Event Day]={'Non-MED'}>}+Interval([End Date]+[End Time]-[Start Date]+[Start Time],'mm')), but I still can't seem to get the right answer. This is what I am getting for using the above expression:

(Please refer to Duration column below)

But the correct one should be (Please refer to Outage Duration column below):

Please please help me and apologies for missing on something obvious.

Thanks in advance!

Re: Difference between two timestamps in minutes?

sudhirpkuwar — Thu, 12 Apr 2018 08:02:15 GMT

Hi,

Try this

interval(date1-date2,'mm')

Re: Difference between two timestamps in minutes?

Anonymous — Thu, 12 Apr 2018 08:10:15 GMT

Hi Sir,

This is how the Excel file looks like:

As you can see, the date and time are on separate cells so I had to add them up first in my expression. I've used the same formula you suggested by I'm not getting the correct answer.

Re: Difference between two timestamps in minutes?

sudhirpkuwar — Thu, 12 Apr 2018 08:15:45 GMT

I thought you want to use it in your straight table ( as shown above)

So I suggested you

can you show me the output for this

interval(Min. Start Date - Max. End Date,'mm')

Re: Difference between two timestamps in minutes?

Anonymous — Thu, 12 Apr 2018 08:19:42 GMT

Yes Sir. What I meant to say was the excel file where the data used in the Straight Table had separate columns for the date and time so when I loaded it into qlikview, I had to combine them by using the + sign.

Since there's not Min and Max Start and End date respectively on the source excel file, the expression doesn't work.

Re: Difference between two timestamps in minutes?

sudhirpkuwar — Thu, 12 Apr 2018 09:38:42 GMT

Can you provide the excel with some sample data...

Re: Difference between two timestamps in minutes?

anagharao — Thu, 12 Apr 2018 09:59:52 GMT

If you are concatenating the date and time you would have to specify the data type of the result. Try:

INTERVAL(DATE([Start Date] + [Start Time]) - DATE([End Date] + [End Time]),'mm.ss')

Re: Difference between two timestamps in minutes?

Anonymous — Thu, 12 Apr 2018 10:06:15 GMT

Thank you Sir. I tweaked it a little, to get the correct answer by using:

INTERVAL(DATE([End Date] + [End Time]) - DATE([Start Date] + [Start Time]),'mm.ss')

Re: Difference between two timestamps in minutes?

anagharao — Thu, 12 Apr 2018 10:13:15 GMT

Great!

Do mark responses from others as helpful or correct. This way they would be encouraged to answer more.