topic Re: Mathematics with date. in QlikView

Mathematics with date.

Thu, 26 Nov 2015 06:37:29 GMT

Hi Experts,

In my script, I have created a field

Interval(num(Date1)-num(Date2),'ss') as Difference

which gives me the difference between the two dates in seconds.

Using the same field I am writing an Expression:

=Sum(Difference)/3600

which converts the sum of difference in dates to hrs.

But what I am getting is Num(Sum(Difference))/3600 which is not required.

How to solve this issue.

hic please see if you can help me on this.

Regards,

Anjali Gupta

Re: Mathematics with date.

its_anandrjs — Thu, 26 Nov 2015 06:40:11 GMT

Hi,

Can you provide some small app to demonstrate this issue so it is easy to look out.

Regards,

Anand

Re: Mathematics with date.

tresB — Thu, 26 Nov 2015 06:43:06 GMT

Interval() formats the timestamp and doesn't change the underlaying value. Try like:

(Date1-Date2)*3600 as Difference

Re: Mathematics with date.

Anonymous — Thu, 26 Nov 2015 06:44:30 GMT

How can you sum the dates??

What is the logic by suming the data and converting it into number and then divide by 3600?? It would not give you right data

First convert your date/Time into hours and then divide it by 3600...

Why you are dividing by 3600??

Re: Mathematics with date.

PrashantSangle — Thu, 26 Nov 2015 06:58:26 GMT

Hi,

If you want difference in hours

then directly you can write

interval(date1-date2,'hh')

try expression suggested by tresesco‌

Regards

Re: Mathematics with date.

Thu, 26 Nov 2015 07:50:47 GMT

Balraj I want the difference in seconds so subtracting, and interval gives me the correct thing .

And i am diving it by 3600 to convert its summation into hrs.

Re: Mathematics with date.

Thu, 26 Nov 2015 07:52:54 GMT

Hi,

Getting the interval(date1-date2,'hh') gives the difference in hours.

Suppose the difference between the two is in muinutes then it will return 0. and summation of 0 won't give me the correct required answer.

Re: Mathematics with date.

Anonymous — Thu, 26 Nov 2015 07:53:00 GMT

Ok, got it. My mistake didnot notice 'ss' in your expression.

Try tresesco suggestion.

Re: Mathematics with date.

jonathandienst — Thu, 26 Nov 2015 09:11:09 GMT

tresesco wrote:

Interval() formats the timestamp and doesn't change the underlaying value. Try like:

(Date1-Date2)*3600 as Difference

That should be

(Date1 - Date2) * 24 * 60 * 60

(Date1 - Date2) * 86400

Re: Mathematics with date.

tresB — Thu, 26 Nov 2015 09:16:25 GMT

Sometimes it happens ! ; being stupid can't be avoided ! But, fortunately there is always someone like you to spot and correct it.

Re: Mathematics with date.

PrashantSangle — Thu, 26 Nov 2015 09:18:53 GMT

Hi,

try interval(date1-date2,'D hh:mm:ss')

Regards

Re: Mathematics with date.

thomaslg_wq — Thu, 26 Nov 2015 09:35:11 GMT

I don't understand what you really need but maybe :

as max dreamer says:

=Interval(date-date,'hh.mm.ss')

Round the hours and change the minutes with 60/100

=(ceil($(differencesecond)/3600))-1+(0.6-(ceil($(differencesecond)/3600)-($(differencesecond)/3600))*0.6)

Re: Mathematics with date.

Thu, 26 Nov 2015 10:07:02 GMT

Thanks Jonathan for the help. I worked the way I wanted.