topic INVERTED DATE FORMAT in QlikView

INVERTED DATE FORMAT

hpwinkelmann — Wed, 26 Jan 2011 09:22:47 GMT

Hello,

I'm trying to use QlikView to create reports from SAP.

Generally this is working without bigger problems, but one SAP table is not easy to use.

In SAP table TCURR the field GDATU is in inverted date format. This means in the table I find the value 79889874 for the 2011-01-25.

Does anybody know how to convert this inverted date into a normal date? Is QlikView having a function for this?

Thanks and regards

INVERTED DATE FORMAT

hector — Wed, 26 Jan 2011 13:27:40 GMT

Following this link https://forums.sdn.sap.com/thread.jspa?threadID=1031339

it says that the number is 99999999 - your_date and this is valid because taking your example

79889874 = 2011-01-25 => 99999999 - 20110125 = 79889874

So using math the date you want is 99999999 - field_value = date YYYYMMDD

so in QlikView will be something like this

date(date#(99999999 - field_value,'YYYYMMDD'))

Hope this helps

Rgds

AW:Re: INVERTED DATE FORMAT

hpwinkelmann — Wed, 26 Jan 2011 14:00:15 GMT

Works perfectly and is so simple.

Thanks very much!

AW:Re: INVERTED DATE FORMAT

hector — Wed, 26 Jan 2011 14:07:02 GMT

Hi again

remember that you can change the format of the date() function with the second parameter, if you use date() without parameters, it's going to take the value from your dateformat variable

SET DateFormat='DD-MM-YYYY';

date(today()) = 26-01-2011

date(today(),'MM-DD-YYYY') = 01-26-2011

Rgds

Re: INVERTED DATE FORMAT

Fri, 02 May 2014 14:45:42 GMT

Es correcto!!

Re: INVERTED DATE FORMAT

Anonymous — Fri, 28 Nov 2014 06:24:50 GMT

Hi,

I have a table Customer of oracle db in which the field Birthday has values as 9.862, 12.532, -856, ...

I have to calculate the Age of Customer.

Can you help me?

Thanks and regards

Re: INVERTED DATE FORMAT

Anonymous — Fri, 28 Nov 2014 06:46:51 GMT

More precisely, I have written:

age(today(),date(BIRTHDAY)) as AGE

but I found that the minimum age is 58 years and the age sorted in a decreasing order by frequency gives the following result:

80 ( freq: 6.589 )

94 ( freq: 5.853 )

92 ( freq: 5.519 )

91 ( freq: 5.413 )

....

ie the clients are too old ...

Re: INVERTED DATE FORMAT

anbu1984 — Fri, 28 Nov 2014 06:57:42 GMT

How did you get values 9.862, 12.532, -856?

Re: INVERTED DATE FORMAT

Anonymous — Sun, 30 Nov 2014 10:06:06 GMT

I have solved the problem ... the values 9.862, 12.532, -856 ... are calculated by SAS that reports the date format in the following way:

- a positive number is the number of days counted after January 1, 1960

- a negative number is the number of days counted before January 1, 1960

Thanks

Re: INVERTED DATE FORMAT

Mon, 01 Feb 2016 21:20:32 GMT

Hi,

I want to convert the created_at_u2 to date format.

Re: INVERTED DATE FORMAT

Mon, 26 Sep 2016 16:06:04 GMT

Hans-Peter,

For the GDATU field in TCURR I used the following to convert.

Which gives you the results :

Best regards,

Ken