Master Calendar: Quarter

[Mini_Elaine]

Hi there,

I would like to create a master calendar for some sales comparisons between different years. I used the follow script: Creating A Master Calendar - Qlik Community - 341286

But I had some problems on the definition of Quarter. I am looking for differences in each financial year (i.e. 01/04/2022-31/03/2023) rather than calendar year. How would I reflect this to master calendar script? 

I was thinking a silly way to rename each quarter but I am sure there must be some easy solutions.

Many thanks!

[Mark_Little]

Hi,

try using the ADDMONTHS() function. Something like the below

CEIL(MONTH(ADDMONTHS(TempDate,3))/3) AS FIN_QUARTER_NO

[Oleg_Troyansky · Accepted solution]

Hi Elaine,

Fiscal quarters (as well as fiscal periods and years) are quite tricky, because there are many different "flavors" of fiscal calendars - 4-4-5, or simply shifting a number of months, etc.

There are several articles in the Blog section of this site, that describe calendars, fiscal years, etc - for example trhis:

https://community.qlik.com/t5/Design/Fiscal-Year/ba-p/1472103

Look for the Design blog, in particular for articles by HIC (Henric Cronstrom) on fiscal calendars - they are brilliant.

What I typically used for fiscal calendars, when they are not driven by the 4-4-5 logic, but rather just shift the year start by a few months, - I'd create a fictitious "date" field that is shifted by a certain number of months - in your case, it would be a shift for 3 months back. So, the "fiscal Date"  01/01 corresponds to the calendar date 01/04. Now, you can calculate all the necessary fields like Fiscal Period, Fiscal Year, Fiscal Quarter, based on the "Fiscal Date" instead of the "calendar Date. Just make sure to always use MonthStart for these calculations, to avoid issues with 31, 30, or 28 days in a given month. For example:

LOAD

Date,     // This is the Calendar Date

AddMonths(Date, -3) as FiscalDate

...

Then, in a preceding load or in a subsequent resident load:

LOAD

Date,

FiscalDate,

num(Month(MonthStart(FiscalDate)))   as FiscalPeriod,

'Q' & Ceil(num(Month(MonthStart(FiscalDate)))/3)  as FiscalQuarter,

etc...

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

[Mini_Elaine]

Thank you @Oleg_Troyansky  and @Mark_Little  for helps! Both work brilliantly. I added the following syntax in my 'Master Calendar' script and it works very well:

'Q' & Ceil(Month(AddMonths (TempDate, -3))/3) as FY_quarter