Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
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;