Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Anonymous
Not applicable
Author

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
sudhirpkuwar
Partner - Creator II
Partner - Creator II

Hi,


Try this

interval(date1-date2,'mm')


Anonymous
Not applicable
Author

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.

sudhirpkuwar
Partner - Creator II
Partner - Creator II

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

Anonymous
Not applicable
Author

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.

sudhirpkuwar
Partner - Creator II
Partner - Creator II

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

anagharao
Creator II
Creator II

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

Anonymous
Not applicable
Author

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

anagharao
Creator II
Creator II

Great!

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