INVERTED DATE FORMAT

[hpwinkelmann]

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

[hector]

Hi

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

[hpwinkelmann]

Works perfectly and is so simple.

Thanks very much!

[hector]

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

[#5]

Es correcto!!

[Anonymous]

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

[Anonymous]

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 ...

[anbu1984]

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

[Anonymous]

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

[#10]

Hi,

I want to convert the created_at_u2 to date format.