topic Re: Subtract two timestamps to output difference in minutes in QlikView

Subtract two timestamps to output difference in minutes

Thu, 15 Nov 2012 12:24:56 GMT

Can anyone assist in subtracting two date/time fields to output difference in minutes.

DD/MM/YYYY hh:mm:ss is my format, but I want it to output just the minutes not the seconds and if it goes into an hour instead of showing 01:10 display 70

Re: Subtract two timestamps to output difference in minutes

Gysbert_Wassenaar — Thu, 15 Nov 2012 12:26:24 GMT

interval(date1-date2,'mm')

Make sure date1 and date2 are really datetimes, use the timestamp# and/or timestamp functions as necessary.

Re: Subtract two timestamps to output difference in minutes

Thu, 15 Nov 2012 12:31:08 GMT

Thanks for the response again Gysbert, they are dates and times

Re: Subtract two timestamps to output difference in minutes

Thu, 15 Nov 2012 12:34:02 GMT

=Minute(Frac(Timestamp1)-Frac(Timestamp2))

i think it ll help u

Re: Subtract two timestamps to output difference in minutes

Thu, 15 Nov 2012 12:37:44 GMT

Hi thanks for the response I tried implementing your suggestion to no avail screenshot below.

Re: Subtract two timestamps to output difference in minutes

Thu, 15 Nov 2012 13:14:46 GMT

Is there any other configuration I need to do first. the field SCH and CHKS are defined within my excel data set as dd/mm/yyyy hh:mm:ss

so far nothing I have tried has rendered a sufficient output

Re: Subtract two timestamps to output difference in minutes

hic — Thu, 15 Nov 2012 13:38:10 GMT

First, check that QlikView has interpreted these dates correctly by formatting them as numbers (ListBox properties -> Numbers). If they are displayed as numbers with values around 40000, then they are correctly interpreted. These numbers are number of days since Dec 30, 1899.

Then you should be able to subract one date from another: Date1 - Date2. Let's assume that the result is 0.1, i.e. 10% of a day, i.e. 2 hours 24 minutes.

Then you can choose to format this number in minutes: Minute(Date1-Date2), which will keep the value 0.1 but display 144, Or, you can convert it to numerical minutes: (Date1-Date2)*24*60

HIC

Re: Subtract two timestamps to output difference in minutes

Mon, 19 Nov 2012 10:42:25 GMT

Hi thanks fior the response,

My fields are formatted dd/mm/yyyy hh:mm:ss so contains both date and time, however only the hh:mm of the time is displayed in the field itself.

I have gone through the formatting and at present it is formatted to fixed to - 2 Decimals.

Re: Subtract two timestamps to output difference in minutes

swuehl — Mon, 19 Nov 2012 11:03:57 GMT

Then you can choose to format this number in minutes: Minute(Date1-Date2), which will keep the value 0.1 but display 144, Or, you can convert it to numerical minutes: (Date1-Date2)*24*60

Hi Henric,

I believe minute() function will always return the minutes part of the interpreted time, so given that 0.1 is interpreted as time 02:24:00 (represented in my standard format),

=minute(0.1)

will return 24, not 144, right? I believe you need to use interval() function for this kind of representation (as Gybert already suggested above):

=interval(0.1,'mm')

sandston, have you checked that your timestamp values are correctly interpreted when reading in from your source (i.e. as formatting them as numbers, like Henric suggested)?

If you don't get numeric values displayed, please check the timestamp interpretation function timestamp#() in the HELP.

As I am writing this, Now() returns 41232.498368056, so f.e.

=interval(41232.498368056 - 41232.448368056,'mm')

should return 72 (which is that two values time difference in minutes, in the format you requested).

Regards,

Stefan

Re: Subtract two timestamps to output difference in minutes

hic — Mon, 19 Nov 2012 12:41:56 GMT

You're right Stefan. Well spotted.

HIC

Re: Subtract two timestamps to output difference in minutes

Mon, 19 Nov 2012 12:43:23 GMT

I believe my settings are correct please advise if I am incorrect.

Re: Subtract two timestamps to output difference in minutes

Mon, 19 Nov 2012 12:45:59 GMT

I should also mention I just wanted the end result for the example above to show -6

I think I have previously stated that I only want minutes not hours as I think I wrote a second ago so if its 1 hour and 15 difference just want it to display 75 minutes.

Before getting to that format however I wanted to get it right

Re: Subtract two timestamps to output difference in minutes

swuehl — Mon, 19 Nov 2012 12:57:03 GMT

You are probably talking about the Delay Mins expression, right?

Why don't you use the interval format option with the format code we suggested ('mm')?

(or use expression default as number format option and use

=interval(sum(...)-sum(...),'mm')

as expression.)

This should also get the sign right.

Re: Subtract two timestamps to output difference in minutes

Mon, 19 Nov 2012 13:01:57 GMT

Hi Swuehi,

I am indeed referring to the expression.

I did try that initially however populated the same result. I feel I have missed a step and am unable to see what the error is.

Re: Subtract two timestamps to output difference in minutes

Mon, 19 Nov 2012 13:08:30 GMT

Hi I think I have managed to figure out where I was going wrong. I had formatted everywhere apart from in the number tab configuring interval to mm

Re: Subtract two timestamps to output difference in minutes

swuehl — Mon, 19 Nov 2012 13:09:30 GMT

So what do you get if you are using this expression? A two digit month number, i.e. 00?

(Please note that AFAIR, 'MM' is the format code for month, not minute...)

Re: Subtract two timestamps to output difference in minutes

Mon, 19 Nov 2012 13:44:06 GMT

Hi Sorry I used lower case mm and it populated the correct information.

If I have an additional query should i start a new thread? its an extension of this query

Re: Subtract two timestamps to output difference in minutes

swuehl — Mon, 19 Nov 2012 13:57:06 GMT

If it's directly related to your OP, you may just add it here.

I general, I personally do prefer one question - one thread.

Re: Subtract two timestamps to output difference in minutes

Mon, 19 Nov 2012 14:28:46 GMT

It is linked but I will start new thread and assign this one as answered. Thanks to everyone for your assistance hoping to get to all your levels in the future