Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Best Practices Re: a Calendar Table and Multiple Date Fields

Howdy.  I've searched through the forum, but not found a post which quite addresses this question.  This one was close, but represented the opposite problem.  The "similar posts" tool found more results han my searching: this post, this one, and this one cover some solutions, but don't discuss which is best, or more generally what advantages and disadvantages exist.  What's easiest?  What's most commonly used?  Has anyone used one of these solutions for 12 months or so and has some feedback on how well it's worked?

A common practice in data warehouses is to have a Dates table which gives values such as year, quarter, and month for each day.  It looks like this is common in QlikView too, and the name "calendar table" seems to be widely-used.  If this calendar table has a Date field as its key, any queries which also contain a Date field will automagically join to it.  Groovy!

But... what do you do when you have multiple fact tables, with different date fields, which should not join together?  For example, say there's a Customers.Date field representing the date the customer was created, and an Orders.Date field representing the date an order was placed.  A report which contains both fields will need to rename at least one of them, breaking the join.

Also, a fact table may contain multiple date dimensions.  In my case, the Orders table has OrderDate, DatePaid, and ShipDate fields.  I could create three more calendar tables, with key fields of OrderDate, DatePaid, and ShipDate, respectively.  This seems cumbersome; is this the best practice, or is there a simpler option?

In SQL, I'd write three JOINs, to the same table each time but specifying a different field from the first table each time.  But QlikView seems to be restricted to natural joins, which as a DBA makes me wince.

Instead of using one or more calendar tables, you could just import the date values as dates, and use QlikView date functions to populate your date hierarchies.  Is this what most users end up using?

I appreciate any input.  Thank you!

30 Replies
johnw
Champion III
Champion III

QlikView handles dates quite poorly in my opinion.  I suspect that QlikTech made the conscious decision that a date field was going to just be a simple field like any other, that they would never create any fields or data or associations for you just because something was a date or for any other reason.  I respect that position, but strongly disagree with it.  Dates and times MEAN something.  I don't know exactly how I'd like to see them work, but certainly they could work better than they do.

Set analysis is an overused tool in my opinion.  It's a great tool, but it's not the best solution to all problems.  Data model changes are often a better solution.

Not applicable
Author

what if the users want to see both shipped and placed in same chart.  I.e.The axis of the chart is the date.  and you want a bar to show Qty Ordered and Qtry Shipped per date.  How will this work if your shipped date and ordered date fields are two different named fields?  What we are doing now is creating a concatenated table with a common date "Report Date" and bring in a row for each Shipped and Ordered and using the script to assign date value:  Order Date as Report Date for order load and then Concatenate the shippe load with Shipped Date as Report Date.  We would either add a field to each load with Type = Order, Type = Shipped  or add a metric column to store a count for Qty Shipped, Qty Ordered.  Is this the only way?

johnw
Champion III
Champion III

I think the attached is very close to what you're doing, including the date type field, but it leaves the original information intact.  In fact, it leaves the original data structure intact.  The new data is additive.  Whether or not preserving the original information and/or data structure is an advantage of course depends on your requirements.

Not applicable
Author

Very helpful!  Thank you John

johnw
Champion III
Champion III

I should probably note that my data model has a synthetic key.  It's hidden because I saved with source table view instead of internal table view.  Swap views to see it clearly.  For the data in my example, I think a synthetic key is exactly what we want, but many people would disagree.  It can be harmlessly removed if desired, and it only surfaced in the first place because Table1 has a two-field key.

Edit: In a more fully-developed data model with existing relationships between the tables, adding a linkage table as per my example might create loops.  Like concatenation, the linkage table is just one possible solution among many to consider.  I chose concatenation to solve this sort of date problem in my most recent real application, for instance.

Not applicable
Author

hI,

First of all, englsih is not my mother language so i apologize in advance for mistakes.

I'm really interrested in your conversation because i face the same problem with calendar. Should i use one master calendar ? Should i build different calendar, one for each fact table ?

In my first application we had registration calendar, first order calendar, sales calendar, AsOfCalendar (to calculate for each date  the week, the month before, the year same date before (18/07/2012 >> 18/07/2011), the same day las year (18/07/2012 >> 19/07/2011) -365 days). I had also to manage calendar year and fiscal year.

For my second application, i have procurement calendar, order calendar, and so on.

So as you can you can we choose multiple calendar. But how do manage all these calendars in your application ? Because with the associative model, if we pick a date in a calendar that impact all tle model.

A lot of usesr forgot to undo selection and complained of missing data (first order sales calendar was selected and when they wen into the sales tab they had only first order sales) So i used trigger to clear calendar and copy the context (firstordersalescalendar.year = 2012 is cleared and sales.year = 2012 is selected and same for quarter, month, week, day ..).

But for me it's not a good solution because it's hard to maintain (sometimes i had to put triggers on button, sometimes on a selected field and so on) .

what do you think about that ? what is the best solution for you ?

Regards,

Not applicable
Author

It sounds like your calendar solution is fine, the real problem is users forgetting about the selections they already have.  To some extent, this is just something they'll have to get used to in QlikView.  You can help them by making their selections prominent; for example, put the date pickers next to each other, so it's obvious when more than one has a selection.  Also, put a Current Selections box on the report, and put it somewhere noticeable.  Show them the Back and Clear buttons.

johnw
Champion III
Champion III

I guess you have registration data on one tab, order data on a second tab, and sales data on a third.  You have three separate calendars and use triggers to copy the calendar selections from one calendar to the next as you switch tabs.  That works, but I understand why you might not like it.

I think you should try a master calendar.  With the master calendar, you wouldn't have to change selections when you change tabs.  It would be simpler.  It might not work like you want, but it is worth a try.

Jonathan's suggestion sounds good too.  You could put every calendar on every tab.  That should make it clear to the users what they need to do.  It can just take a lot of space.

The best solution depends on the application.  I've used master calendars.  I've used separate calendars.  I've used separate calendars with triggers that copy selections from one calendar to another.

Not applicable
Author

Thank you for your quick and helpful answer. I wiil try to enhance the calendar selection, but i don't know if i show every calendar on every tab is a good solution, risk of overload. I should try

But how does a master calendar work ?  How i understand it, it's a detached calendar, but how do you link it to the other tables ? triggers ?

johnw
Champion III
Champion III

Please see the "testMultipleDatesMultipleTablesOneCalendar3.qvw" example earlier in the thread.  It isn't detached, though that CAN cause loops in your data structure, so isn't always a good solution.

A detached calendar, or "date island" as we often say around here, is another possibility.  It's one I very rarely use due to performance issues and expression complexity, but it's doable.  So say you have RegistrationDate, OrderDate and SaleDate.  Now you make a new field, Date, and that's the field you let them select (along with Week, Month, Year, whatever you need).

Then in all of your charts, link up this island date to the dates you care about with an if() statement, such as sum(if(SaleDate=Date,SaleAmount)).  (Edit: To clarify, as Anosh Nathaniel says in the next post, the if() and associated performance penalty is only necessary on charts with the Island Date as a dimension.  Other charts can use set analysis, as then all you're trying to match is the selections, not the dimensions.)

Actually, see the attached example.  I expanded the earlier example to include an island calendar for comparison.