Canonical calendar note question

[EMaebe]

 I've got the canonical calendar working in my model, but I'd appreciate if someone can take a look at my notes that I've written out and confirm that I'm fully understanding or if I've mis-spoken anywhere. I had a hard time fully understanding the canonical calendar post everyone references (here: https://community.qlik.com/t5/Design/Canonical-Date/ba-p/1463578), so I wanted my own notes in my own words for future reference because that's how I learn. Here are my notes. I'm hoping someone can proofread for me before I use them to create a canonical calendar in other apps going forward: 

The canonical calendar allows a user to connect multiple dates to the the master calendar and filter the data by different date types (in the below example, Dispatch date, Order Add Date, etc). Instead of connecting your master calendar and fact table by one date, you connect your fact table and master calendar to an intermediary synthetic table (which I'm calling my Date Bridge table).

Here is the data model view of my canonical calendar:

The date bridge table uses the fact table as the resident table it is pulling the dates from (you could have multiple resident tables depending on the # of different tables that have dates that need to be included inside of your date type).The Date Bridge is before the Master Calendar in the script.

Inside of the date bridge table, you have your "bridge key" between the fact table and the date bridge table. The bridge key is a unique ID of some type inside of the fact table. In this case, my unique identifier for the fact table is  ordnum&ordlin&shipID.

Also inside of the date bridge table is a DateKey between the master calendar and the date bridge table. The date key  are the dates from the tables that you want to be connected to the master calendar and must be in the same date format between all tables ($date $numeric tag in Qlik as well as the same date format such as 'MM-DD-YYYY').

In the fact table:

The date type is the filter for the different dates that you're bringing into the date bridge table. (in this case, the  'dispatch date' or the 'order add date').

Thus, to create a canonical calendar:

1. Identify and add a bridge key to the fact table (unique ID)
2. Add the date key to the master calendar (and format)
3. Create your date bridge table and load as distinct the bridge key, the date key, and name the date type. Use resident table(s) and the dates you want to use in the date key. One per load.
4. Make sure that the dates are formatted the same (both date format and string format) between the fact table, date bridge table, and master calendar.
5. Check your work.

[rubenmarin]

Hi, I think your notes are correct, just to add some things:

Instead of "distinct(BridgeKey)" I would use "distinct(BridgeKey) as BridgeKey", the distinct keeps the field name but I prefer to specify it in case it changes on some version.

For the date key you can convert dates to numbers, so you don't need to worry of date formats: Num(Floor(DateField)) as DateKey

It's not really needed to apply the same date formats in the fact table, but it's recommended in case that dates are shown in some table, to keep consistent date formats.

And to complete the notes I would add an example of use in the expressions, like: Sum({$<DateType={'Dispatched'}>} Amount)

And also I would recommend to avoid the use of spaces in fields used as codes, use 'OrderAdded' instead of 'Order Added'.

[marcus_sommer]

I don't want to say that your deduction is wrong but it may misleading in some way. Therefore I would keep the description of the above mentioned blog-posting as reference as well as copy & paste (it may not live forever) within the documentation. Further adding that the provided approach isn't a general solution to handle multiple date-fields else it covered just the main-idea to combine multiple dates - which have a relationship between each other - within a single dimension.

This means each scenario of data + requirements needs an own evaluation which kinds of calendars are possible and more or less suitable. This regards to logically reasons because only linked date-fields (like event-chains of order + billing + shipment) are suitable for a canonical approach but not mandatory further dates from a CRM respectively those dates may need an own canonical calendar. So depending on data + requirements you may end with multiple canonical calendars and also multiple "normal" calendars.

Also from a technically point of view it may not be the best choice to apply the canonical approach with a bridge-table respectively a link-table approach. Link-tables could become much larger as the fact-table(s) which may result in a lower UI performance, they won't globally prevent the "lost" of data within all views (by missing key's) and also the efforts to develop and maintain the data-models could be more expensive and complex as with other data-model approaches. An alternatively might be to avoid the canonical logic by using a data-stream architecture by concatenating all / the important facts to each other within a star-scheme data-model which may look like:

ID	Type	Date	KPI	Value
1	Order	01.01.2023	Amount	10
1	Order	01.01.2023	Sales	100
1	Billing	02.01.2023	Payment	100
1	Shipment	03.01.2023	Amount	10

which means no extra table with a combined key is needed and it exists only a single date-field which could be connected to a normal calendar. Each kind of differentiation within the UI could be done with the type-field as dimension, selection and/or set analysis condition.

In my experience could such star-scheme logic much easier be adapted to different data- and reporting requirements.

Nevertheless if you have now a working solution keep it and the next time you may consider it just as one possibility within the design-decision.

[EMaebe]

I am really struggling with understanding how to handle multiple dates that I will need to use in metrics calculations. In this case, I think order add and dispatch date are the "link chained dates" that you mentioned as they're transactional dates when an order flows through the warehouse so when the order was added to when it was dispatched.

You mention multiple ways to approach calendars, I have always used a fiscal calendar table (a table maintained in our data warehouse) and connected to the appropriate date field in the model. If there are two or more dates that I'll need to be able to look at using the fiscal calendar then I've used the bridge. Are you saying in some cases I should just bring in the fiscal calendar multiple times and have separate calendars? How do I handle this in the sheets when building visualizations? Name the filters like "Order Add Fiscal Year/Month" vs "Dispatch Fiscal Year/Month"? My concern is bringing in the fiscal calendar multiple times would lead to confusion when the business user is filtering the sheets that I've built. 

Any recommendations on learning more about the data-stream architecture that you mentioned? I'm not fully following your description and am having a hard time visualizing what the data model would look like or how to implement that or when to use it. 

Thank you for your thoughtful response!

[EMaebe]

Thank you! I'll make those adjustments and add the example. I do use these dates in tables and will also be doing calculations between the dates (i.e. how much time has elapsed between the order being added and the dispatch date). 

I've heard to not use spaces in code before, but I've never been told why. I'm self-taught 😅 so any explanation as to why not to use spaces in code would be greatly appreciated!  

Thank you for your response!

[marcus_sommer]

I did not relate to fiscal calendars else only canonical calendars and "normal" calendars. In regard to your question I suggest not to treat a fiscal calendar differently as a normal calendar because it's often the same unless an offset of n days or month. In many cases both information - normal and fiscal - could be kept in one calendar.

Nevertheless you may also need a separate fiscal calendar and like above mentioned multiple ones depending on the data and requirements. I didn't say that you need multiple calendars within your current use-case else my aim was to hint that there aren't simple and general applying solutions of handling multiple date-fields.

To end with multiple calendars is not mandatory a sign of an insufficient designed data-model else it may just the opposite because the date-fields are not always compatible with each other. Quite often it's not possible to show complex data-relations within a simplified manner else the users will need a deeper knowledge of them to be able to interpret the data correct. This means we need to be careful not to oversimplify the reports (a very wise man said ones: keep the things as simple as possible but not simpler ...).

[marcus_sommer]

Spaces or special chars like operators like - / are delimiter for the parser indicating that a new field/table/operation should be addressed. In such cases it needs extra measurements to show the parser where a statement starts and ends und this is usually done with various kinds of brackets and/or quotes. In some cases the statements itself contain such bracket/quote and/or they might be nested which is usually done with a further wrapping but the syntax-complexity increased.

So avoiding spaces and special chars could simplify the work but in regard to the readability of field/table-names and so on it's also important to name them unambiguously and therefore spaces and special chars might be helpful - so both facts needs to be balanced against each other ...

- Marcus

[EMaebe]

---

@marcus_sommer wrote:

I did not relate to fiscal calendars else only canonical calendars and "normal" calendars. In regard to your question I suggest not to treat a fiscal calendar differently as a normal calendar because it's often the same unless an offset of n days or month. In many cases both information - normal and fiscal - could be kept in one calendar.

---

I have both the fiscal calendar and "normal" calendar in that table in the data warehouse, but we mainly use our fiscal calendar. It's a 5-4-4 type accounting calendar and is offset to october for fiscal start date. 🙂 

Thank you for all of your help! I've definitely got some reading up I'm going to need to do on data model designs as I'm definitely still in the new/learning process. I appreciate your thoughtful responses!