topic Re: Bad Date Output in QlikView

Bad Date Output

Thu, 02 Jul 2015 14:30:14 GMT

Can anyone explain what it happening when I concatenate two date fields together.

The dates are coming from the same table. Here's what I'm doing with the first

date(date#(DateField1,'MMMYY'),'M/1/YYYY') as InvoiceDate

I get the results that I want. It converts JAN15, FEB15, MAR15, etc... to 1/1/2015, 2/1/2015, 3/1/2015, etc...

The conversion I'm doing for the second date is the standard date conversion

Date(DateField2) as InvoiceDate which outputs a list of dates with the same format above but it's a date for every day

1/1/2015

1/2/2015

1/3/2015

etc...

When I concatenate the two together, the results come out like this. No individual dates, just a duplicate list of dates for the first day of the month

1/1/2015

2/1/2015

3/1/2015

etc.....

Does any have a clue as to what is happening and how to fix this?

Re: Bad Date Output

MK_QSL — Thu, 02 Jul 2015 14:33:22 GMT

Use

=MonthStart(date#(DateField1,'MMMYY'))

Re: Bad Date Output

jonathandienst — Thu, 02 Jul 2015 14:40:06 GMT

Manish's suggestion should work, but if not I would change to this:

date(date#(DateField1,'MMMYY'),'M/D/YYYY')

Re: Bad Date Output

giakoum — Thu, 02 Jul 2015 14:40:11 GMT

date(date#(DateField1,'MMMYY'),'M/1/YYYY') as InvoiceDate

change 1 to D :

date(date#(DateField1,'MMMYY'),'M/D/YYYY') as InvoiceDate

Re: Bad Date Output

marcus_sommer — Thu, 02 Jul 2015 14:40:18 GMT

I would do the second load first and/or do the same formatting like in the first load. Qlikview applied to the whole field the format from first record. You will find more informations here: How to use - Master-Calendar and Date-Values.

- Marcus