Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor

Master Data Calendar and set analysis with last month, last year


I would like to create a masterdata calendar and use date for create set analysis with last mont, last year etc.

I have a file with Date with a format YYYYMM

I created a Date DD/MM/YYYY like this 01&'/'&right([Date],2)&'/'&left([Date],4) AS Date,

After that I created a script to create a Data Table  :

Temp: //Using the fact to extract min and max date

LOAD Min(Date) as minDate,

          Max(Date) as maxDate

Resident FactTable;

LET vMinDate = Num(Peek('minDate', 0, 'Temp'));

LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;



  $(vMinDate) + IterNo() - 1         as Num,

  Date($(vMinDate) + IterNo() - 1)     as TempDate


  1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);



  TempDate as Date,

  Week(TempDate) as Week,

  Year(TempDate) as Year,

  Month(TempDate) as Month,

  Day(TempDate) as Day,

  Weekday(TempDate) as WeekDay,

  'Q' & ceil(month(TempDate) / 3) as Quarter,

  'Q' & Ceil(Month(TempDate)/3) & '-' & Year(TempDate) as QuarterYear,

  MonthName(TempDate) as MonthYear,

  Week(TempDate)&'-'&Year(TempDate) as WeekYear

Resident TempCalendar

Order By TempDate ASC;

DROP Table TempCalendar;

Questions :

  • Is it the better solution to create a Master Data calendar ?
  • Now how can I created value for set analysis:
    • last month
    • last year
    • last 12 month
  • How use this value in set analysis

Many thanks for your answer, it is a pleasure to read lot of article here.