topic Re: Interval Date Issue in QlikView

Interval Date Issue

Mon, 22 Dec 2014 09:32:15 GMT

Hello,

I am facing an issue with sum().

I have minutes calculated as

round(Interval(Date1-Date2,'mm')*(24*60)) as IntervalMinutes

For an data it comes 32 but when i write sum(IntervalMinutes) it shows 64.

Any idea

Re: Interval Date Issue

its_anandrjs — Mon, 22 Dec 2014 09:37:07 GMT

The dates are you using it is timestamp.

Regards

Anand

Re: Interval Date Issue

Mon, 22 Dec 2014 09:40:55 GMT

Yes Of Course

Re: Interval Date Issue

Anonymous — Mon, 22 Dec 2014 09:43:22 GMT

What are the values of Date1 & Date2 and why multiply by 24*60 ?

Re: Interval Date Issue

Mon, 22 Dec 2014 09:45:20 GMT

Previously i was getting 0 as difference hence used this logic

Re: Interval Date Issue

Mon, 22 Dec 2014 09:46:08 GMT

Main thing is that when the value is 32 by a after multiplication then why it converts to 64 when sum()

Re: Interval Date Issue

anbu1984 — Mon, 22 Dec 2014 09:47:39 GMT

If difference is zero, then how did you get 32 after multiplication?

Can you provide sample input or app

Re: Interval Date Issue

Mon, 22 Dec 2014 09:48:59 GMT

Please find the attached Document

Re: Interval Date Issue

Mon, 22 Dec 2014 09:55:24 GMT

Did not work

Re: Interval Date Issue

Mon, 22 Dec 2014 09:57:18 GMT

Even i tried this

Interval(Timestamp(Date1,'M/D/YYYY h:mm:ss[.fff] TT')-Timestamp(Date2,'M/D/YYYY h:mm:ss[.fff] TT'),'mm')*(24*60))

Re: Interval Date Issue

anbu1984 — Mon, 22 Dec 2014 10:01:46 GMT

Can you post sample app

Re: Interval Date Issue

Anonymous — Mon, 22 Dec 2014 11:19:18 GMT

The output of Interval() is a QlikView Timestamp so 32 minutes is stored numerically as 32/60/24 which = 0.022222.... And the Round() function forces it to display as numeric decimal, which will be 0 after the rounding.

The Sum() functions though should leave it displaying in minute format, so this :

=sum ( interval ( timestamp(timestamp#('02-Dec-2014 10:42' , 'DD-MMM-YYYY hh:mm'))

- timestamp(timestamp#('02-Dec-2014 10:10' , 'DD-MMM-YYYY hh:mm')) ,'mm' ) )

displays an output of 32.

Given the above what is it that you wish to achieve with this expression ?

Re: Interval Date Issue

Mon, 22 Dec 2014 11:53:39 GMT

Bill its ok i get 0.0222222 I round up it after multiplying by 24*60 and then i round off. It comes 32 Now when i use expression sum() the minutes i get 64

Re: Interval Date Issue

Mon, 22 Dec 2014 11:54:58 GMT

@Bill Markham

Re: Interval Date Issue

Mon, 22 Dec 2014 11:57:40 GMT

Hi King,

take a close look at your underlying data, bring in a rowno() field, I think you will find you have duplicate rows coming through, when looking at the underlying data you will only see the single row, hence the 32, but actually 2 exist which is being calculated within the sum and giving you the 64

hope that helps

Joe