Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Qlik Community,
I hope this finds you well. I would like to add a quarteryear function to my mastercalendar. As the data set that I work with deals with multiple years, it would be useful to be able to analyse via quarteryear i.e. Q12016,Q22017 etc...
Would you by any chance know what expression would allow for that? I tried several combinations using the Quarter function in my mastercalender but no luck! Your help would be much appreciated. Thank you!
Please find enclosed the calendar that I am currently using for your reference:
MinMax:
LOAD Min([CP Date]) AS MinDate,
Max([CP Date]) AS MaxDate
RESIDENT [CP Page];
LET vMinDate = Num(Peek('MinDate',0,'MinMax'));
LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));
MasterCalendar:
LOAD
Date($(vMinDate) + RecNo() - 1) AS [CP Date],
Year(Date($(vMinDate) + RecNo() - 1)) as [Year],
Month(Date($(vMinDate) + RecNo() - 1)) as [Month],
Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MMM-YYYY') AS [MonthYear],
ceil(month(Date($(vMinDate) + RecNo() - 1))/3) as Quarter
AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);
DROP TABLE MinMax;
Dear Mohammed,
Try the below script
'Q'&ceil(month(Date($(vMinDate) + RecNo() - 1))/3)&'-'&Year(Date($(vMinDate) + RecNo() - 1)) As QuarterYear
example script:
[CP Page]:
LOAD *,
Date(Date,'DD/MM/YYYY') AS [CP Date]
Inline
[
Date
01/01/2014
01/01/2017
]
;
MinMax:
LOAD Min([CP Date]) AS MinDate,
Max([CP Date]) AS MaxDate
RESIDENT [CP Page];
LET vMinDate = Num(Peek('MinDate',0,'MinMax'));
LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));
MasterCalendar:
LOAD
Date($(vMinDate) + RecNo() - 1) AS [CP Date],
Year(Date($(vMinDate) + RecNo() - 1)) as [Year],
Month(Date($(vMinDate) + RecNo() - 1)) as [Month],
Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MMM-YYYY') AS [MonthYear],
ceil(month(Date($(vMinDate) + RecNo() - 1))/3) as Quarter,
'Q'&ceil(month(Date($(vMinDate) + RecNo() - 1))/3)&'-'&Year(Date($(vMinDate) + RecNo() - 1)) As QuarterYear
AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);
DROP TABLE MinMax;
There is no default function.
You will have to concatenate Quarter with year.
Regards,
Kaushik Solanki
='Q'&Ceil(Month(Date) / 3)&Year(Date)
Dear,
As mentioned by kaushik.solanki there is no default function to get QuarterYear from Date.
But in you can use the below script
ceil(month(Date($(vMinDate) + RecNo() - 1))/3)&''&
Year(Date($(vMinDate) + RecNo() - 1)) As QuarterYear,
Thanks,
Mukram
Thank you all for your help! Mukram I tried your expression and got the right number of quarters that should be in the data set but they come out in this format:
12016
12017
22016
22017
32015
32016
32017
42015
42016
When I converted those to a 'Date' function within the UI
I got:
23/11/1932
24/11/1932
10/04/1960
26/08/1987
27/08/1987
28/08/2987
11/01/2015
12/01/2015
Is there some way to make the quateryear date function produce dates in a format closer to Q1-2015,Q2-2016 etc...
Thank you all again for your help.
Best,
Mohammed
Dear Mohammed,
Try the below script
'Q'&ceil(month(Date($(vMinDate) + RecNo() - 1))/3)&'-'&Year(Date($(vMinDate) + RecNo() - 1)) As QuarterYear
example script:
[CP Page]:
LOAD *,
Date(Date,'DD/MM/YYYY') AS [CP Date]
Inline
[
Date
01/01/2014
01/01/2017
]
;
MinMax:
LOAD Min([CP Date]) AS MinDate,
Max([CP Date]) AS MaxDate
RESIDENT [CP Page];
LET vMinDate = Num(Peek('MinDate',0,'MinMax'));
LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));
MasterCalendar:
LOAD
Date($(vMinDate) + RecNo() - 1) AS [CP Date],
Year(Date($(vMinDate) + RecNo() - 1)) as [Year],
Month(Date($(vMinDate) + RecNo() - 1)) as [Month],
Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MMM-YYYY') AS [MonthYear],
ceil(month(Date($(vMinDate) + RecNo() - 1))/3) as Quarter,
'Q'&ceil(month(Date($(vMinDate) + RecNo() - 1))/3)&'-'&Year(Date($(vMinDate) + RecNo() - 1)) As QuarterYear
AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);
DROP TABLE MinMax;
Thank you everyone!