QuarterYear function in Master Calender

[malradi88]

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;

[kaushiknsolanki]

There is no default function.

You will have to concatenate Quarter with year.

Regards,

Kaushik Solanki

[dan_sullivan]

='Q'&Ceil(Month(Date) / 3)&Year(Date)

[mdmukramali]

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

[malradi88]

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

[mdmukramali · Accepted solution]

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;

[malradi88]

Thank you everyone!