Solved: Re: Change Date format - Qlik Community

tracycrown · ‎2016-01-18

Dear Sir/Madam

Kindly advise how to convert report date YYYYMMDD to following formats in scrip :

1. MMM-YY

2. YYYY

3. MM

4. DD

Thank you

Tracy

Peter_Cammaert · ‎2016-01-23

Ah, that's a common weirdness in QlikView. And actually it isn't weird at all. All fields in QlikView are of type dual, meaning that they have both a numerical value and a string representation (or format). The MonthYear values in your data model are in reality dates with a day component. The first one is Oct 12, 2013, the second one is Oct 18, 2013. The string formatting that you use in your load script doesn't change the numerical value, but will assign a MMM-YY format string to that field.

The field values though remain different, while their representation looks the same. All objects in QlikView will list/order dimensions first on their numerical value.

Simple to solve: change the load script for MonthYear into something like:

LOAD Date,

Date(MonthStart(Date), 'MMM-YY') as MonthYear,

:

and gone is your issue.

Best,

Peter

View solution in original post

sunny_talwar · ‎2016-01-18

Try this:

Table:

LOAD Date,

Date(Date, 'MMM-YY') as MonthYear,

Year(Date) as Year,

Month(Date) as Month,

Num(Month(Date)) as MonthNum,

Day(Date) as Day;

LOAD Date#(Date,'YYYYMMDD') as Date,

Amount

FROM

Test.xlsx

(ooxml, embedded labels, table is All);

Report Inappropriate Content · ‎2016-01-18

First you need to convert your Input date field with DATE# function and then use date functions.

Please try like below:

LOAD

*,

Date(Date,'MMM-YYYY') as YearMonth,

Year(Date) AS Year,

Num(Month(Date),'00') AS Month,

Num(Day(Date),'00') AS Day

;

LOAD

Date#(Date,'YYYYMMDD') AS Date,

Amount

FROM [Test (1).xlsx] (ooxml, embedded labels, table is All);

maxgro · ‎2016-01-18

add an interpretation function (Date#) to your script to

Date(Date#(Date, 'YYYYMMDD'),'MMM-YYYY') as YearMonth

and some other useful help on dates

Get the Dates Right

tracycrown · ‎2016-01-21

Dear Sunny

The sum(Amount) is not working, please advise.

Thanks

Tracy

tracycrown · ‎2016-01-22

Dear Dathu

Your method seems ok but it will not be correct for following data especially in Chart for October month (two bars instead of one). Also, please advise how to generate quarterly data.

Date	Amount
20130802	2000
20131012	30000
20130701	2500
20130215	4200
20131018	295
20140703	15000
20140219	2389

Thank you

Tracy

mightyqlikers · ‎2016-01-22

Dear Tracy,

Please find the below script

Directory;

LOAD Date,

date(MakeDate(Left(Date,4),Mid(Date,5,2),Right(Date,2)),'DD-MM-YYYY') as DT,

date(MakeDate(Left(Date,4),Mid(Date,5,2),Right(Date,2)),'MMM-YYYY') as MMMYYDT,

Date(Date,'MMM-YYYY') as YearMonth,

Amount

FROM

Test.xlsx

(ooxml, embedded labels, table is All);

Regards

$@M

tracycrown · ‎2016-01-22

Dear Maxgro

Your method does not sort the month in ascending order.

MonthYear	sum(Amount)
Jan-2013	2000
Aug-2014	2389
Jun-2014	15000
Oct-2013	30000

Thank you

Tracy

mightyqlikers · ‎2016-01-22

Try sorting by

sort month year by DT field

Regards
$@M.

tracycrown · ‎2016-01-22

Dear Sam

YearMonth is not correct.

YearMonth	sum(Amount)
May-57014	2000
Nov-57016	30000
Feb-57043	15000
Sep-57043	2389