Create an AUTO CALENDAR TABLE IN QLIK

[kravmagas]

Guys, I'll will appreciate a lot for your support in advanced. Recently the company it is evaluating the purchase of BI tools, Of course, I'm more inclined to select qlik, than the other BI tools. In middle of this WAR PowerBI, hit me hard, when in a "soft" course I receive, we could create a "CALENDAR TABLE" with only one function:  CALENDARAUTO()... The first field create ALL THE RECORDS for the whole calendar year, from January First to December 31st. EASILY.

So, I need to know if there is a way to create a table in Qlik that through a FUNCTION create all the records of a calendar table. As you can SEE in the attached image.

Calendar Table Create Automatically from a Function

It is Possible to "duplicate" this process in qlik? (of course with qlik function).

The is a procedure to create that in qlik automatically? Or I have to create the table outside and the attached to the project?

Any help will be appreciate, please, help me to beat OTHERS BI tools... jejeje 🙂

regards

Pedro Garcia

(Sorry for my english, some time it not very good looking)

[vmoreno2605]

I dunno if there is a simple function, what i do have is this code:

//Here i get the min and max date from my datasource (in this case any table that has the dates you need) so that i establish a limit for how many dates are going to be generated

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

TempCal:
LOAD
Date($(vMinDate) + RowNo() - 1) as TempDate
AutoGenerate
$(vMaxDate) - $(vMinDate) + 1;

MasterCalendar: 
Load
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
Week(TempDate,6) as Week, //The number 6 means that my week starts on sunday... i dont really remember
If(WeekDay(TempDate)='Lun',1,
If(WeekDay(TempDate)='Mar',2,
If(WeekDay(TempDate)='Mie',3,
If(WeekDay(TempDate)='Jue',4,
If(WeekDay(TempDate)='Vie',5,
If(WeekDay(TempDate)='Sab',6,
If(WeekDay(TempDate)='Dom',7))))))) as WeekNumber, 
WeekDay(TempDate) as DayWeekName,
TempDate as Date,
Num(TempDate) as DateNumber,
Num(Month(TempDate)) as MonthNumber,
Year(TempDate)&' - '&if(Num(Month(TempDate))<10,'0'&Num(Month(TempDate)), Num(Month(TempDate))) as Year_Month
Resident TempCal
Order By TempDate;

Drop Table TempCal;

There are some other fields that i use and i didn't include, this can be the most basic way to create a master calendar. You can add anything you want to it, for example, here i add the last two days of each month

Left Join(MasterCalendar)
Load Year, Month, Max(Day) as LastDay, Max(Day,2) as AlmostLastDay
Resident MasterCalendar
Group by Year, Month;

Another example here, how many saturdays every month has

Left Join(MasterCalendar)
Load Year_Month, sum(SumadorSabado) as CantidadSabados
Resident MasterCalendar
Group by Year_Month;

Ass you can see, there is not an especific function but you can do many things creating your own master calendar with code.

I hope this helps bro

[hectormunoz]

Hi

in Qlik there are these options:

1. Create a calendar manually, with only for the values in the date field, this means it's going to have 365 values for a whole year in the best case scenario (Exists records for every date).
2. Create a calendar automatically using the "autocalendar" function in the script
3. Create a calendar using an autogenerate table with a start date and an end date
4. Use the library QlikView Components, or QVC where you can find a set of many functions that will help in your everyday developments

If you want to compare "function" vs "function", your best options are 2 and 4 in the load script, not in the layout.

Hope this helps.

Regards.

[vmoreno2605]

Hola! Mira, que yo sepa no hay como tal una funcion para crear automaticamente un calendario, yo lo llamo masterCalendar (asi me referire a el en el codigo). Lo que yo hago es que tengo una aplicacion que tiene un script que genera un masterCalendar y el archivo que crea esa aplicacion lo mando a llamar a todas donde lo voy a requerir, el archivo creado es un QVD (Si no sabes que son los QVD, revisalos, son muy utiles para la carga rapida de datos)

Mira, este es el codigo que yo utilizo para crear mi calendario

//Estas variables obtienen la fecha maxima y minima de alguna tabla que contenga fechas, en este caso pues deberian de ser las fechas que voy a usar, si tienes varias tablas con varios campos de fechas, ahi acomoda para obtener la mas vieja y la mas actual

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

//Aqui utilizo esas variables y genero una tabla que contiene todas las fechas desde la Min hasta la Max.

TempCal:
LOAD
Date($(vMinDate) + RowNo() - 1) as TempDate
AutoGenerate
$(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

//Y aqui simplemente con el campo generado juego con la informacion de la manera que yo requiera.

MasterCalendar:
Load
Year(TempDate) as Año,
Month(TempDate) as Mes,
Day(TempDate) as Dia,
Week(TempDate,6) as Semana,
If(WeekDay(TempDate)='Lun',1,
If(WeekDay(TempDate)='Mar',2,
If(WeekDay(TempDate)='Mie',3,
If(WeekDay(TempDate)='Jue',4,
If(WeekDay(TempDate)='Vie',5,
If(WeekDay(TempDate)='Sab',6,
If(WeekDay(TempDate)='Dom',7))))))) as NumeroDiaSemana,
WeekDay(TempDate) as NombreDiaSemana,
TempDate as Fecha,
Num(TempDate) as FechaNumerica,
Num(Month(TempDate)) as MesN,
Year(TempDate)&' - '&if(Num(Month(TempDate))<10,'0'&Num(Month(TempDate)), Num(Month(TempDate))) as AñoMes
Resident TempCal
Order By TempDate;

Drop Table TempCal;

Como puedes ver, no es mucho codigo, se le pueden agregar las cosas que necesites, un ejemplo seria este

Left Join(MasterCalendar)
Load AñoMes, sum(SumadorSabado) as CantidadSabados
Resident MasterCalendar
Group by AñoMes;

//Ahi agrego por ejemplo, cuantos sabados tiene cada mes, quiza no todas las aplicaciones lo necesiten, pero hay algunas que si, entonces al momento de cargar la informacion a la aplicacion simplemente mandas a llamar los campos que necesitas. La ventaja de crearlo con codigo es que puedes generar los campos que necesites y mandarlos a llamar. Te dejo aqui la imagen del que yo genero para que veas la cantidad de cosas que se pueden agregar, aclarando que no todos son utilizados en todas las apps.

Espero y te ayude, saludos!

[kravmagas]

Lo reviso y te cuento como me fue, por ahora me da error en la siguiente linea, pero quizas no estoy invocando bien el nombre del campo en la tabla.

15:08:34 A

Autogenerate: generate count is negative: TempCal: LOAD Date( + RowNo() - 1) as TempDate AutoGenerate - + 1

Este es el Campo que estoy evaluando

LET vMinDate = Num(Peek(SAF_FECGRA, 0,SAF_FECGRA));
LET vMaxDate = Num(Peek(SAF_FECGRA, 0, SAF_FECGRA));

SAF_FECGRA

[gileswalker]

Did you ever get a satisfactory reply to this?

There is a stable solution in place for this issue now (Jan-22) that I have been using a while.  I use it in every app I create because it is so good.

Basically in the load script editor, underneath the last line of your complete load statement, you need to insert the below syntax.  It will give you mostly all the date permutations you may require.  I have had to only modify once or twice to satisfy localized requirements for slightly unusual date set ups.  The only bit you need to modify is the last line where I have put in bold.....just put in the date fields you need to be able to use the autoCalendar on that are in your load statement.  Having this in place makes set expression much easier because you are easily able to calculate things like sum of sales X number of 'months ago' which makes calculations dynamic.

Be aware that this will leverage itself on your data, using the settings you have in place the load script editor (at the top) for SET FirstWeekDay=0; SET BrokenWeeks=0; SET ReferenceDay=4; SET FirstMonthOfYear=1;

HTH

[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),

Dual(Year($1) & '-' & Num(Week($1),00),WeekStart($1)) AS [YearWeek],

Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [*Insert your date field name 1*], [*Insert your date field name 2*], [*Insert your date field name 3*], [*Insert your date field name X*] USING [autoCalendar] ;