Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

antoinevunc
New Contributor

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

Hello,

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;

TempCalendar:

LOAD

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

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

AutoGenerate

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

MasterCalendar:

LOAD

  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.