Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

joeybird
Contributor II

link calendar table

Hiya

i have three tables

orders:

OrderID  OrderDateTime

Packing

PackingID  PackingDateTime

Shipments

ShipmentID  ShipmentDateTime





i want these two tables to share the same calendar...and use same e.g financial year filter pane to add up e.g count (orderID)


i do not wish to rename change the fields so they match, as you end up with more e.g in a KPI ....COUNT(ORDERID)



i do not wish to rename all the same , ShipmentDateTime, PackingDateTime, OrderDateTime to eg datetime as it causes a synthetic key


i have seen this


Tutorial - Using Common Date Dimensions and Shared Calendars

i have seen the tutorial


but all i get is a circular reference.


i seen many tutorials and no joy



is there any code that outlines a master calendar that can be used to link multiple tables based solely on date


please help

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: link calendar table

Joanna,

I'm sure you missed something if you're getting synthetic keys and/or circular references.  From your description, all you need is a master calendar (there are plenty examples on the forum) with a key field, let's call it Date, and rename all of your date fields to Date, removing the time part this way:

floor(OrderDateTime) as Date

floor(PackingDateTime) as Date

floor(ShipmentDateTime) as Date

(Depending on the format of the Date field in the calendar, you maybe need to add date() function to the above.)

Edit: Second thought - you probably have already some common fields in Orders, Shipments, and Packing tables that you forgot to mention.  If this is the case, you can concatenate all three into one table.

2 Replies
mov
Esteemed Contributor III

Re: link calendar table

Joanna,

I'm sure you missed something if you're getting synthetic keys and/or circular references.  From your description, all you need is a master calendar (there are plenty examples on the forum) with a key field, let's call it Date, and rename all of your date fields to Date, removing the time part this way:

floor(OrderDateTime) as Date

floor(PackingDateTime) as Date

floor(ShipmentDateTime) as Date

(Depending on the format of the Date field in the calendar, you maybe need to add date() function to the above.)

Edit: Second thought - you probably have already some common fields in Orders, Shipments, and Packing tables that you forgot to mention.  If this is the case, you can concatenate all three into one table.

joeybird
Contributor II

Re: link calendar table

Thank you - Brill