Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Re: Date Conversion strange behaviour

Hi Rob,

Thanks for having a look at this.

I understand what you said but I've a question:

Suppose the file also has some dates formatted as :12/06/30 (YY/MM/DD i.e. 30 June 2012)

then if we use : "DD/MM/YYYY" earflier, this date will be formatted as 12/06/30 (12 June 2030).

So,how to deal with this ?

Thanks again


Re: Date Conversion strange behaviour

Well, how can you as a human being tell which format to apply when parsing in?

Is there any other information in the record that gives you a hint?

Not applicable

Re: Date Conversion strange behaviour

Hi Stefan,

I just look at the different formats in the file and apply the alt function without considering the order of the formats.

I think your answer suggests that it can't be solved simply by using QV logic/functions and some human intervention has to be used.

What i can do here is ask the people who fills the date to use one of the format and not both.

Am i correct ?



Re: Date Conversion strange behaviour

Given your two formats in your example, 12/06/30 is a valid value in both date formats. So there is an ambiguity if you want to get the 'true' date.

It's always a good thing if your input data is in a well defined format, so if you can get your users to use one format, that would be much better than guessing around when reading in, right?

If possible, use a four digit year format. QV will use some AI to determine the full four digit year (e.g. determine 2013 from 13). I believe it will interprete 63 - 99 as being in 20th century, and 0 to 62 in 21st century (range is +-50 years from today).

Maybe this is according to what your users expect, maybe not.

And finally, there is an ISO standard for dates, which avoids the DD/MM/YY(YY) resp. MM/DD/YY(YY) etc. ambiguity altogether: 'YYYY-MM-DD'

Not applicable

Re: Date Conversion strange behaviour

Hello Stefan,

As always your answer is an authority for me to ask the users to fill in a single date format.

Should I finally ask for the ISO standard: YYYY-MM-DD ?

then in the QV application I can simply use:  date(alt(date#(DateField,'YYYY-MM-DD'),0)

0 for date when the field is blank.

Is this setup ok ?


Not applicable

Re: Date Conversion strange behaviour


This kind of issues occur whn working on Excel sources, u have gt lots of expert advices,

I have my HR analysis partially dependent on excel files, my personal experience says put the blame on the users and demand them to follow one file / column format, tht is the only option u have with u

or else u work on it andf stable the files once received atleast for date column issue.

Rest good luck.

Community Browser