How to make a master calender for this

[ss2q]

I want to make a master calendar so when I choose Year as filter and choose 2022 it should pick results for that year from two tables and show.

I have two files called Invoice and Order and the common field between them is Order Number. It's Order_No in the invoice file and ORDER_NUMBER in the order file. I created their relationship with the data manager but I don't know how to make the master calendar and how to use it.

I extracted the year from GL_DATE from the invoice file and the year from ORDERED_DATE from the order file but for my analysis, I want to use GL_DATE year as my filter and it should automatically match with ORDERED_date year.

So, I will be grateful if someone can tell me how to do it in step by step.

[Antoine04]

Hello,

What you need to do is create a common calendar for both dates.

It exists several solutions for it. Attached you will find the Qlik doc with the best practices.

Hope it helps.

Regards,

Antoine L

[Pablo007]

Hi @ss2q ,

You shoud create and script as master calendar,  there are several models that you can copy paste

Here you can find some options

Solved: How to Create Master Calendar ? - Qlik Community - 103109

or you can use below one

On main add like this (sorry is in Spanish but you can change it at your convienience:

Set Hoy=Today();
Set FechaInicioCarga=YearStart(addYears($(Hoy), -5));

then create a new section :New script "Calendario"

Unqualify 'Fecha';
Unqualify 'DiaFecha';
Unqualify 'MesFecha';
Unqualify 'AñoFecha';

Calendario:
LOAD DISTINCT
date($(FechaInicioCarga)+RecNo()-1) as #Fecha,
date($(FechaInicioCarga)+RecNo()-1) as Fecha,
Day(date($(FechaInicioCarga)+RecNo()-1)) as DiaFecha,
Month(date($(FechaInicioCarga)+RecNo()-1)) as MesFecha,
Year(date($(FechaInicioCarga)+RecNo()-1)) as AñoFecha
AutoGenerate YearEnd($(Hoy))-$(FechaInicioCarga)+0
// AutoGenerate ($(Hoy))-$(FechaInicioCarga)+365
;

Then you can make a relationship between new Calendario Table and yours