Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor III

Difference between two timestamps in minutes?

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)

1.PNG

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

2.PNG

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

Thanks in advance!

1 Solution

Accepted Solutions
Highlighted
New Contributor III

Re: Difference between two timestamps in minutes?

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')

View solution in original post

8 Replies
Highlighted
Partner
Partner

Re: Difference between two timestamps in minutes?

Hi,


Try this

interval(date1-date2,'mm')


Highlighted
New Contributor III

Re: Difference between two timestamps in minutes?

Hi Sir,

This is how the Excel file looks like:

3.PNG

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.

Highlighted
Partner
Partner

Re: Difference between two timestamps in minutes?

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')

Highlighted
New Contributor III

Re: Difference between two timestamps in minutes?

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.

4.PNG

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

Highlighted
Partner
Partner

Re: Difference between two timestamps in minutes?

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

Highlighted
Contributor II

Re: Difference between two timestamps in minutes?

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')

Highlighted
New Contributor III

Re: Difference between two timestamps in minutes?

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')

View solution in original post

Highlighted
Contributor II

Re: Difference between two timestamps in minutes?

Great!

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