topic Re: convert date in QlikView

convert date

Thu, 23 Oct 2014 13:35:24 GMT

how can i calculate this instruction:

1-1.1*(date_debut-date_fin)/date_fin as offf

the problem is in the date i couldn't convert it into a number.

Re: convert date

Anonymous — Thu, 23 Oct 2014 13:40:12 GMT

Try this:

1-1.1*(floor(num(date_debut))-floor(num(date_fin)))/floor(num(date_fin)) as offf

Re: convert date

Anonymous — Thu, 23 Oct 2014 13:44:52 GMT

Use num function to covert it into a number

1-1.1*(num(date_debut)-num(date_fin))/num(date_fin) as offf

Re: convert date

Thu, 23 Oct 2014 13:52:15 GMT

no it doesn't work, for exampl if

date_debut:17:26:19

date_fin:120:00:00

i must have 1,94,but when tried the functions that you suggested i've got: 3,65

Re: convert date

MarcoWedel — Thu, 23 Oct 2014 13:55:26 GMT

The question is what you like to achive with this calculation, as it will deliver nearly 1 for all contemporary timestamps.

hope this helps

regards

Marco

Re: convert date

Anonymous — Thu, 23 Oct 2014 13:55:51 GMT

I don't recognize that date format. What are the 3 (:) delimited parts?

Re: convert date

Thu, 23 Oct 2014 13:56:09 GMT

Have you checked that the dates are represented correctly before you convert them into numbers?

Re: convert date

Thu, 23 Oct 2014 13:57:42 GMT

it's time not date hh:MM:SS

Re: convert date

its_anandrjs — Thu, 23 Oct 2014 13:57:43 GMT

Try this way also

Load

1 - 1.1 * ( Date( date_debut ) - Date( date_fin ) ) / Date( date_fin ) as offf

From Location;

Regards

Anand

Re: convert date

Jason_Michaelides — Thu, 23 Oct 2014 14:01:00 GMT

What kind of timestamp is "date_fin:120:00:00"?

Re: convert date

Anonymous — Thu, 23 Oct 2014 14:02:56 GMT

Hi,

As u mentioned i substituted the values for that fields in a text object ,got 1.94

1-1.1*(num('17:26:19')-num('120:00:00'))/num('120:00:00')

Re: convert date

Thu, 23 Oct 2014 14:03:27 GMT

you can considrate any time 4 example: 10:11:13

Re: convert date

its_anandrjs — Thu, 23 Oct 2014 14:07:58 GMT

Then convert it into Hour, Minutes or seconds from your requirement in the formula

Regards

Anand

Re: convert date

Anonymous — Thu, 23 Oct 2014 14:10:03 GMT

1-1.1*(subfield(date_debut,':',1)*3600+subfield(date_debut,':',2)*60+subfield(date_debut,':',3)-subfield(date_fin,':',1)*3600+subfield(date_fin,':',2)*60+subfield(date_fin,':',3))/(subfield(date_fin,':',1)*3600+subfield(date_fin,':',2)*60+subfield(date_fin,':',3)) as offf

Re: convert date

Anonymous — Thu, 23 Oct 2014 14:18:24 GMT

@youssef assenane:

is the above expr still not working?

Re: convert date

MarcoWedel — Thu, 23 Oct 2014 14:27:05 GMT

=1-1.1*('17:26:19'-'120:00:00')/'120:00:00'

Re: convert date

Thu, 23 Oct 2014 14:27:09 GMT

it's still not working

Re: convert date

Thu, 23 Oct 2014 14:31:23 GMT

how you get that

Re: convert date

Fri, 24 Oct 2014 09:43:41 GMT

but why when i do =1-1.1*(DUREE_INCIDENT-duree_trajet)/duree_trajet in text objet i get -27,176