topic Re: Date format change in qlik? in QlikView

Date format change in qlik?

Thu, 12 Nov 2015 13:07:49 GMT

Hi community,

I have date column in my csv file in two formats,

1.format = mm/dd/yyyy

2.number format= 41730,

monthname(Date(alt("Inv Date", Date(Date#("Inv Date", 'MM/DD/YYYY'), 'MMM-YYYY'),

Date(num("Inv Date"), 'MMM-YYYY') ))) as "Invoice Date" ,

used above script to change the format,

when i am trying to change the format to mmm-yyyy, system is getting confused to take some records

for example : 10/01/2015(oct 1st 2015) but it is loading as Jan 2015(Jan 10th 2015).

And am not sure how the number format is changing.

Please help me how can I resolve this.

Re: Date format change in qlik?

sunny_talwar — Thu, 12 Nov 2015 13:16:52 GMT

May be this:

MonthName(Alt(Date#("Inv Date", 'MM/DD/YYYY'), "Inv Date")) as "Invoice Date" ,

Re: Date format change in qlik?

Anonymous — Thu, 12 Nov 2015 13:17:40 GMT

Try this:

=date(NumberFormatField,'MMM-YYYY') as NumberFormatField

Re: Date format change in qlik?

sunny_talwar — Thu, 12 Nov 2015 13:20:27 GMT

or even this:

SET DateFormat='MM/DD/YYYY';

MonthName(Date("Inv Date")) as "Invoice Date"

Re: Date format change in qlik?

Thu, 12 Nov 2015 13:43:32 GMT

Hi sunny,

thanks for the reply,

first 10 days of oct are taking as different months,

10/01/2015 as 01/10/2015 (i need Oct 1st)

10/12/2015 as 10/12/2015( I need Oct 12th).

thank you

Re: Date format change in qlik?

punitpopli — Thu, 12 Nov 2015 13:45:28 GMT

Try this :

date(Addmonths(Date#(Date,'Format'),0),'Format')

Re: Date format change in qlik?

Thu, 12 Nov 2015 13:47:53 GMT

Hi Balraj,

Not working,

I have two formats in same column.

10/01/2015

41750

I need to change both to MMM-YYYY format

Thnak you

Re: Date format change in qlik?

Thu, 12 Nov 2015 14:14:53 GMT

Hi Punit,

this is the output am getting when I used the above expression

Re: Date format change in qlik?

Peter_Cammaert — Thu, 12 Nov 2015 15:20:45 GMT

Care to post some sample csv data?

Re: Date format change in qlik?

Thu, 12 Nov 2015 17:08:34 GMT

Hi,

sample data:

Inv date:(MM/dd/yyyy) Output am getting Expected output

10/01/2015 Jan-2015 Oct-2015

10/02/2015 Feb-2015 Oct-2015

10/03/2015 Mar-2015 Oct-2015

10/04/2015 Apr-2015 Oct-2015

10/05/2015 May-2015 Oct-2015

10/06/2015 Jun-2015 Oct-2015

10/07/2015 Jul-2015 Oct-2015

10/08/2015 Aug-2015 Oct-2015

10/09/2015 Sep-2015 Oct-2015

10/10/2015 Oct-2015 Oct-2015

10/11/2015 Nov-2015 Oct-2015

10/12/2015 Dec-2015 Oct-2015

from 10/13/2015 are storing in Oct month.

thank you

Re: Date format change in qlik?

sunny_talwar — Thu, 12 Nov 2015 17:13:36 GMT

This should work for you:

Date(Date#([Inv date], 'MM/DD/YYYY'), 'MMM-YYYY') as [Expected Output]