6 Replies Latest reply: Aug 14, 2017 4:50 AM by Mohammed Al Radi

# QuarterYear function in Master Calender

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:

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;

• ###### Re: QuarterYear function in Master Calender

There is no default function.

You will have to concatenate Quarter with year.

Regards,

Kaushik Solanki

• ###### Re: QuarterYear function in Master Calender

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

• ###### Re: QuarterYear function in Master Calender

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

• ###### Re: QuarterYear function in Master Calender

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

• ###### Re: QuarterYear function in Master Calender

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]:
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:
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;

• ###### Re: QuarterYear function in Master Calender

Thank you everyone!