Data Format Issue

[Khwaja]

Hi,

As per my data, the date is in text format which is  not recognized by Qlik so I used date#(field,'dd/mm/yy'). this solved my problem but my goal is to show the date in Quarters, months and years. The issue is qlik is reading every date as a 1st month (Jan) and 1st quarter. any help on this?

Script I used:
Date#(ISSUEDATE,'dd/mm/yyyy') as Document_Date,
year(date#(ISSUEDATE,'dd/mm/yyyy')) as Document_Year,
year(date#(ISSUEDATE,'dd/mm/yyyy'))&'-Q'&ceil(month(Date#(ISSUEDATE,'dd/mm/yyyy'))/3) as Document_Quarter,
monthname(floor(date#(ISSUEDATE,'dd/mm/yyyy'))) as Document_Month,


Result:

[Or]

Your issue is probably with the format mask. For dates, you should be using DD/MM/YYYY (Uppercase, not lowercase). Lowercase 'mm' refers to minutes.

[pallavi_96]

Hi Khwaja,

You can create the master calendar to get this resolved.

Date:
Load * Inline
[
Opendate
01/01/2020
08/01/2020
04/04/2019
07/01/2017
];

NoConcatenate
Format:
Load
Date#(Date(Opendate,'DD/MM/YYYY'),'DD/MM/YYYY') as Opendate,
Date#(Date(Opendate,'DD/MM/YYYY'),'DD/MM/YYYY') as Date
Resident Date;

drop table Date;

Quartersmap:
Mapping Load
RowNo() as Month,
'Q' & Ceil(RowNo()/3)as Quarter
AutoGenerate (12);

Temp:
LOAD
Min(Opendate) as Mindate,
Max(Opendate) as Maxdate
Resident Format;

LET vmindate= Num(Peek('Mindate',0,'Temp'));
LET vmaxdate= Num(Peek('Maxdate',0,'Temp'));

DROP Table Temp;

Calendar:
LOAD
Date($(vmindate) + IterNo() -1) as Date
AutoGenerate 1
While
($(vmindate) + IterNo() -1)<= $(vmaxdate);

Master_Cal:
LOAD
Date as Opendate,
Year(Date) &'-'& ApplyMap('Quartersmap',Month(Date),Null()) as QuarterYear,
Monthname(Date) as Monthyear
Resident Calendar;

DROP Table Calendar;

exit script;