Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
malradi88
Contributor

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:

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;

1 Solution

Accepted Solutions
mdmukramali
Valued Contributor III

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

6 Replies

Re: QuarterYear function in Master Calender

There is no default function.

You will have to concatenate Quarter with year.

Regards,

Kaushik Solanki

dan_sullivan
Contributor II

Re: QuarterYear function in Master Calender

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

mdmukramali
Valued Contributor III

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



malradi88
Contributor

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

mdmukramali
Valued Contributor III

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

Re: QuarterYear function in Master Calender

Thank you everyone!

Community Browser