Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

why my date field for Jan only consist from 1/1/16 till 1/12/16 ?

Hi All

I am here not to ask for solution , as i am already found the solution. but i don't know why ? Hope some one can share with me.

I have below script , which is wrong :-

Date(Date#([TempDate],'M/D/YYYY')) as YearMonthDay, 

From above script , only those date from 1St jan till 12 Jan have sales amount , date after Jan 13 all become missing value.

So i guess it is due to my date field should be D/M/yy instead of M/D/yy ?

Date(Date#([TempDate],'D/M/YYYY')) as YearMonthDay,  

Can it work fine, now 1st jan till 30 Jan will have sales amount. and no more missing value.

Can some one share with me why ?

Paul

1 Solution

Accepted Solutions

Re: why my date field for Jan only consist from 1/1/16 till 1/12/16 ?

Because based on what you have mentioned your date is D/M/YYYY and if you are going to ask QlikView to read it as M/D/YYYY it will only recognize it till 12/01 (because for QlikView it is Dec 1st), but 13/01 is null for QlikView because there is no 13th Month in the calendar.

Is this what you are trying to understand? Or I just made up stuff for you

9 Replies

Re: why my date field for Jan only consist from 1/1/16 till 1/12/16 ?

The date format that guides your app is the one defined in the main page

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

for example.

Try to modify that format and see what happen

Re: why my date field for Jan only consist from 1/1/16 till 1/12/16 ?

The value isn't missing, it is still there in the app, but it is not associated to a date. The reason is that when you do Date#() and provide a format and the format doesn't match the date becomes null. So Jan 13 onward data is all associated with a null date.

Does it make sense?

Re: why my date field for Jan only consist from 1/1/16 till 1/12/16 ?

Your Dates end jan 12 because month (12) is the largest value you have defined for day.

paulyeo11
Valued Contributor II

Re: why my date field for Jan only consist from 1/1/16 till 1/12/16 ?

Hi Sunny

Thank you for your sharing. I am aware that 13 Jan is not group as Jan month. But after I modify the script 13 Jan till 30 Jan group under Jan month. The issue is I not understand why after I modify script and the format date become correct.

Paul

Sent from my iPhone

Re: why my date field for Jan only consist from 1/1/16 till 1/12/16 ?

Because based on what you have mentioned your date is D/M/YYYY and if you are going to ask QlikView to read it as M/D/YYYY it will only recognize it till 12/01 (because for QlikView it is Dec 1st), but 13/01 is null for QlikView because there is no 13th Month in the calendar.

Is this what you are trying to understand? Or I just made up stuff for you

paulyeo11
Valued Contributor II

Re: why my date field for Jan only consist from 1/1/16 till 1/12/16 ?

Hi Sunny

Okay now I understand , because it is due to date is month = 13 is not understand by QV. Now I understand , thank for clear my doubt .

Paul

Sent from my iPhone

Re: why my date field for Jan only consist from 1/1/16 till 1/12/16 ?

Not a problem my friend, I am glad I was able to help you understand

paulyeo11
Valued Contributor II

Re: why my date field for Jan only consist from 1/1/16 till 1/12/16 ?

‌Hi Sunny

you said you are born in India and live in US , but your photo look like you look like American , why ?

Re: why my date field for Jan only consist from 1/1/16 till 1/12/16 ?

Hahahaha you think my photo looks like an American . All my Indian friends will disagree with you.

Community Browser