Calendar with multiple date fields from multiple tables

[neerajthakur]

Hi,

I have 40-50 different date fields across approx. 30 tables all of them are connected by some key/ref_id.

Now I need to create a common calendar using which I can fetch records. Like in filter pane if I select fiscal year 2022 - 2023 all the records having date in fiscal year 2022 - 2023 should be displayed, once I achieved something near to this by building a date bridge with dates as DateKey, DateType with FieldName and keys. and joining all dates with one main table and then connecting calendar to that, but got other fiscal year too in chart even after restricting using set analysis on DateType for that measures.

Like for any license used in 2022 - 2023 and expiring in 2025 - 2026 it displays both the years if I write a measures to calculate licenses issued in a trend chart.

Both license issued date and expiry date are in same table linked to application created table via ref id. This application table contains all the dates and keys which I have separated out from other tables.

I have also tried creating two Auto Calendar one for normal year and other for fiscal year but can't use it as it will not have one fiscal year , year, month , quarter which I can give in filters for users to chose from. 

Issue is urgent pls respond if you can help me solve it asap.

@stevedark @PrashantSangle @sunny_talwar @MayilVahanan @Saravanan_Desingh @vikasmahajan @Taoufiq_Zarra @ashfaq_haseeb @tresesco @Kushal_Chawda 

[stevedark]

Hi @neerajthakur 

Ideally you want a single table in which has a single column for date, all of the date breakdowns (Month, Year etc.), the type of date that row is and then link keys to the other tables. This may, or may not, be possible in your case.

It sounds like this is what you have attempted to do, so we need to pick through why it hasn't worked.

First up, there is no need for a separate calendar table, or auto calendar. I would just create the date parts as you create that table.

This key table can be built using resident loads when other loads have completed, or if you have a decent QVD layer in place you can load from each QVD twice, once into its own table and once into the Events link table.

Something like:

CONCATENATE(Events)
LOAD
   'Licence Start' as [Date Type],
   [Licence Start] as Date,
   Year([Licence Start]) as Year,
   { other date parts here, inc. fiscal periods }
   LicenceID,
   CustomerID
RESIDENT Licences;

CONCATENATE(Events)
LOAD
   'Licence End' as [Date Type],
   [Licence End] as Date,
   Year([Licence End]) as Year,
   { other date parts here, inc. fiscal periods }
   LicenceID,
   CustomerID
RESIDENT Licences;

Once this has been done you will need to drop any fields that will cause a synthetic key. In the example above CustomerID is in the licences table, but is now redundant as they key is in the Events table. Do this like this:

DROP FIELD CustomerID FROM Licences;

Your events table should, once complete, have all dates from all tables and as many ID keys as are required to pull everything together.

There are often complexities in getting this kind of model to work and, as I said at the top, it doesn't work in every circumstance - but it often does.

Hope that helps.

Steve

[neerajthakur]

I have tried this approach by building a date bridge with Field Name as Date Type , Date as DateKey and  a key and joining the bridge with Date of Master Calendar but it isn't working in the supposed manner.

[vikasmahajan]

Hi ,

You can try with Link table concept :  

https://community.qlik.com/t5/Qlik-Sense-Documents/link-table/ta-p/1883375

please find attached script to create master calender  fiscal year

also you can keep 

  [Licence Start] as Date,

&

 'Licence End' as [Date Type],  // to identify the date type records

as stevedark mention.

Thanks

Vikas

[stevedark]

Hi @neerajthakur 

You can do away with the master calendar step by building that into your bridge table.

I tend to build a Date Parts variable, like this:

// Set a function for getting date parts built
set vDateParts = Date(DayStart([$1]), 'DD MMM YYYY') as [Date],
Date(Floor([$1],1/24), 'DD MMM YYYY hh:mm') as [Date Hour],
Date(MonthStart([$1]), 'MMM-YYYY') as [Month],
Year([$1]) as [Year],
Month([$1]) as [Month Name],
WeekDay([$1]) as [Weekday],
Hour([$1]) as [Hour];

That can be expanded to have all of your fiscal periods in as well. The code above can then change to be:

CONCATENATE(Events)
LOAD
   'Licence Start' as [Date Type],
   $(vDateParts(Licence Start)),
   LicenceID,
   CustomerID
RESIDENT Licences;

You could also have it so that you pass in a prefix for each date part as a second parameter, but you would not want that in this case.

I would suggest building up slowly, starting with two dates and get that working. Then add the rest from the first table and get that working. Then try bringing in another table.

By doing it step by step you should be able to see where it breaks.

Steve