topic Re: Date & Calender in QlikView

Date & Calender

Mon, 06 Apr 2015 12:35:35 GMT

Dears,

Good day,

I hope every one in good health , and i need to know something about date if the date stored in our database as a number like this(115001,115002,115003,.......,,115046)

how we can convert it to date (year ,month , day ....) what is the formula & logically how it work .

Ahmad Kastero

Thanks For All

Re: Date & Calender

sunny_talwar — Mon, 06 Apr 2015 12:43:33 GMT

What date does 115001 corresponds to?

Best,

Sunny

Re: Date & Calender

sunny_talwar — Mon, 06 Apr 2015 12:45:12 GMT

If this is a number format then it corresponds to 11/10/2214. So my guess is, this is not a number format date you have stored in your database.

Re: Date & Calender

Mon, 06 Apr 2015 12:47:09 GMT

this is a number format ( in the application ERP it is a date but when store it in the database it convert it to number )

Re: Date & Calender

Mon, 06 Apr 2015 12:50:09 GMT

some friend solve it and convert it by using this ((date(yearstart(makedate((((SLTRDJ - fmod(SLTRDJ,1000)) / 1000) + 1900))) + (fmod(SLTRDJ,1000) - 1)) as Date,) but know he leave the company and i tray to understand it , this code already return the date . but how it work ?!

Re: Date & Calender

oknotsen — Mon, 06 Apr 2015 12:50:15 GMT

Qlik stores dates as numbers.

1 = December 31st 1899

Today (April 6th 2015) = 42100

If your system is also storing dates as numbers, but with a different starting point, calculate the different and subtract that from your number.

Re: Date & Calender

sunny_talwar — Mon, 06 Apr 2015 13:17:53 GMT

So 115001 seems to equal 1/1/2015 according to your formula. Is that correct?

Best,

Sunny

Re: Date & Calender

Anonymous — Mon, 06 Apr 2015 13:40:03 GMT

I see - the first three digits (115) is the number of years after 1900. That is 115 is 1900+115= 2015

The last three digits (001) is the number of day in the year. So, a simpler version of expression is:

=date(makedate(left(SLTRDJ,3)+1900) + right(SLTRDJ,3)-1)

Re: Date & Calender

maleksafa — Mon, 06 Apr 2015 13:53:20 GMT

this is julian date format, what is the database that you are using? because in my case we also have this date format in sql server database, and what i am doing is converting this date to datetime when extracting the data from sql.

Re: Date & Calender

mjayachandran — Tue, 07 Apr 2015 08:13:09 GMT

Its called JD Edwards date format very similar to Julian Date.

If you want to understand what it is and how the converstion is done please go throught the link below:

JD Edwards Date Conversions (CYYDDD) | Kirix Strata Blog

you can use a simple formula in excel to convert it to readable format using :

=DATE(1900+(A1/1000),1,1)+RIGHT(A1,3)-1

in Qlikview it is by using

=date(makedate(left(115003,3)+1900) + right(115003,3)-1)

Re: Date & Calender

Tue, 07 Apr 2015 08:28:49 GMT

we have Oracle ERP System , But i connect to the server database and read it by using SQL server and it is look like this format (115001,115002,115003) and when we use that formula it retrieve the correct data , but didn't understand it before until they explain it (hank you msolomov).

Re: Date & Calender

Wed, 15 Apr 2015 12:19:51 GMT

thank you msolomov and thank you all ,and i need to ask you after this formula if i need to convert the normal date to numeric value with the same logic that i get the date .

what i mean is revers the the formula to get the numeric value .

because i face an issue after convert it when i try to join the date withe the other table but have normal date and i need to trust after convert the date to the numeric value is the same so can any one help me in this