Year showing as 1905

[juleshartley]

Hi all,

I'm struggling with my date handling! I have a year field that I'd ONLY like to be an integer eg. 2014. However QlikView is determined to display it as 1905. If I use the expression Num() or Text() in the front end then it displays correctly. I have tried 'forcing it' by using Dual, Num etc. in the load script but nothing seems to help...!

NB: I inherited this app and the field was called 'Year', which I think is partly the cause of the problem... it seems to be ok if I rename it.

[MK_QSL]

Let us have the format of the Date field from which you have derived Year Field

[#3]

Hi,

In script u can use num(Date) as Date_New

[juleshartley]

As mentioned, I have tried all of these things. I think it must be something to do with the crosstable function forcing a field called 'year' to be a date...

[ashfaq_haseeb]

Hi,

Can you post sample data to look over.

Regards

ASHFAQ

[Gysbert_Wassenaar]

Your year is probably a date that gets interpreted wrong. For example year(2014-01-01) would become year(2012) since 2014 -1 -1 = 2012. That's 2012 days after december 31st 1899: july 4th 1905. The year is then 1905.

[Anonymous]

Yes, i have the same problem.

How it can be solved?

I'm joining for request for help

Tahnk You in advance !

Jacek Antek

[Peter_Cammaert]

Did you test Gysberts suggested solution in this same thread here: Re: Year showing as 1905?

[swuehl]

The solution is pretty simple:

Carefully look at the argument you are feeding into Year() or any date functions that expects a date as argument:

Is this argument really something that can be interpreted as date:

Why don’t my dates work?

Or do I need to help QV by using interpretation functions with a correct format code?

And is the interpretation correct? For example if you just input a year value like 2016 into Year() field, it can of course be interpreted as date (see above link for date explanation, having text and number representation), but this is not what you want and expect.

So always double check what the function expects as argument and what you input.

[Oleg_Troyansky]

Hi Hartley,

your dates must be Julian... Just kidding 🙂

Here are my two cents about this problem... A quick empirical test shows that in QlikView

Year(2016) = 1905  (2016 days since January 1st, 1900)

So, I speculate that you may have a field that was already formatted as a year earlier, and then enclosed in a Year function once again, causing this unexpected result.

I'd trace all the transformations of this field back to the beginning, or simply replace the field altogether with a new Year calculation - it's easy to just calculate Year(MyDate) and forget about the whole mystery.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!