Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
mibb1234
Contributor II
Contributor II

Custom periods and master calendar

I have a table with periods and dates. The columns are fixed but sometimes period 13 can be null . I need the master calendar to incorporate fiscal year and period (1-13) based on this data.  I am not sure of the best way to approach this.

As a temporary measure I created an excel with a list dates and the associated period but ideally I would like it to be dynamic and based off this table but  I'm not sure how to adapt the master calendar. I searched other posts but they  seem to be based on a fixed rule rather than a table of start and end dates.

Any pointers or tips would be much appreciated.

Thanks

 

fiscal_yearper_start##1per_end##1per_start##2per_end##2per_start##3per_end##3per_start##4per_end##4per_start##5per_end##5per_start##6per_end##6per_start##7per_end##7per_start##8per_end##8per_start##9per_end##9per_start##10per_end##10per_start##11per_end##11per_start##12per_end##12per_start##13per_end##13
201702/10/201629/10/201630/10/201603/12/201604/12/201631/12/201601/01/201728/01/201729/01/201725/02/201726/02/201701/04/201702/04/201729/04/201730/04/201703/06/201704/06/201701/07/201702/07/201729/07/201730/07/201702/09/201703/09/201730/09/2017NULLNULL
201801/10/201728/10/201729/10/201702/12/201703/12/201730/12/201731/12/201703/02/201804/02/201803/03/201804/03/201831/03/201801/04/201828/04/201829/04/201802/06/201803/06/201830/06/201801/07/201828/07/201829/07/201801/09/201802/09/201829/09/2018NULLNULL
201930/09/201803/11/201804/11/201801/12/201802/12/201829/12/201830/12/201802/02/201903/02/201902/03/201903/03/201930/03/201931/03/201927/04/201928/04/201901/06/201902/06/201929/06/201930/06/201903/08/201904/08/201924/08/201925/08/201928/09/201929/09/201929/09/2019
202030/09/201902/11/201903/11/201930/11/201901/12/201928/12/201929/12/201901/02/202002/02/202029/02/202001/03/202028/03/202029/03/202002/05/202003/05/202030/05/202031/05/202027/06/202028/06/202001/08/202002/08/202029/08/202030/08/202003/10/2020NULLNULL
202104/10/202006/11/202007/11/202005/12/202006/12/202002/01/202103/01/202106/02/202107/02/202106/03/202107/03/202103/04/202104/04/202108/05/202109/05/202105/06/202106/06/202103/07/202104/07/202107/08/202108/08/202104/09/202105/09/202102/10/2021NULLNULL

 

 

 

CalendarMaster:
LOAD
    Date(InvoiceDate) AS InvoiceDate,
    Year(InvoiceDate) AS Year,
    'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter,
    Month(InvoiceDate) As Month,
    Day(InvoiceDate) As Day,
    Week(InvoiceDate) As Week;

Load Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load
    Min(InvoiceDate) AS MinDate,
    Max(InvoiceDate) AS MaxDate

RESIDENT Invoice;

 

  

0 Replies