Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

MASTER CALENDER

arvind1494
Valued Contributor

MASTER CALENDER

QuartersMap: 

MAPPING LOAD  

rowno() as FYMonth, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12);

Temp: 

Load 

               date(yearstart(min(InvoiceDate))) as minDate,  // InvoiceDate is a field in your table with name OSBSTD

               max(InvoiceDate) as maxDate 

Resident OSBSTD;

//OSBSTD; 

 

Let vMin = Num(Peek('minDate', 0, 'Temp')); 

Let vMax = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCal:

Load

Date(Date('$(vMin)')-1 + recno())  as Temp_Date

AUTOGENERATE (Date('$(vMax)')-Date('$(vMin)'))+1;

MasterCalendar:

LOAD Temp_Date as [InvoiceDate],

     Year(Temp_Date) as [Year],

     day(Temp_Date)as day,

     'Week' &week(Temp_Date) as Week,

    // peek(Week) as week2,

     //num(Month(Temp_Date),'00') as Cal_Month,

     MonthName(Temp_Date) as [Month Year],

     //ApplyMap('QuartersMap', month(Temp_Date), Null()) as Quarter,

     ApplyMap('QuartersMap', Dual(Month( Date(Date#("Temp_Date",'MM/DD/YYYY'))),if(Month( Date(Date#("Temp_Date",'MM/DD/YYYY')))<4,Month( Date(Date#("Temp_Date",'MM/DD/YYYY')))+9,Month( Date(Date#("Temp_Date",'MM/DD/YYYY')))-3)), Null()) as Quarter,

     Month(Temp_Date) as [Month],

     Year(Temp_Date)&num(Month(Temp_Date),'00')  as [MonthYear] ,

    if(wildmatch(Dual(Month( Date(Date#("Temp_Date",'MM/DD/YYYY'))),if(Month( Date(Date#("Temp_Date",'MM/DD/YYYY')))<4,Month( Date(Date#("Temp_Date",'MM/DD/YYYY')))+9,Month( Date(Date#("Temp_Date",'MM/DD/YYYY')))-3)),'Apr','May','Jun','Jul','Aug','Sep'),'H1','H2') as HalfYear

    

     ,

     Year(YearStart( Date(Date#("Temp_Date",'MM/DD/YYYY')),0,4)) as FYYear,

     Dual(Month( Date(Date#("Temp_Date",'MM/DD/YYYY'))),if(Month( Date(Date#("Temp_Date",'MM/DD/YYYY')))<4,Month( Date(Date#("Temp_Date",'MM/DD/YYYY')))+9,Month( Date(Date#("Temp_Date",'MM/DD/YYYY')))-3))

as FYMonth

    

//      Year(YearStart( Date(Date#("Temp_Date",'YYYYMM')),0,4)) as Year_FY,

// YearName( Date(Date#("Temp_Date",'YYYYMM')),0,4) As Finacial_Year,

// Dual(Month( Date(Date#("Temp_Date",'YYYYMM'))),if(Month( Date(Date#("Temp_Date",'YYYYMM')))<4,Month( Date(Date#("Temp_Date",'YYYYMM')))+9,Month( Date(Date#("Temp_Date",'YYYYMM')))-3))

// as FYMonth

 

    

    

Resident TempCal;

DROP Table TempCal;

Version history
Revision #:
1 of 1
Last update:
‎2018-01-29 05:35 AM
Updated by: