I would really appreciate the community's input here - one on the solution offered by myself and its viability and two on the data modelling issue . . . as I am clearly not getting my head around it!
Thinking through my own question above, I came to the conclusion that trying to extract the relevant information from a date and timestamp using QV would prove to be useless, as the underlying data would still be a date and timestamp (and it is only the presentation that is changed), and hence would not function as expected as a field selector - click on and select a specific "Month" to reduce the dataset to only that "Month".
No problem, I felt that an elegant solution may be to create an Excel file with the date and the desired selector fields calculated based on the date. I could then link the different tables that imported a date to the Master_Date table and viola, I would have universal access to the selector fields through the key to the Master_Date table.
Alas, this did not work as planned and I got an error to say that there were loops in the data model. I have attached this as a QVW file with no data.
So. firstly is this idea of a Master_Date and related selector fields appropriate? If not, what are alternatives?
And secondly, could someone advise how best to link to this Master_Date table to access the selector fields without the loops? At some stage I will want to link the Timesheet_Hours table to a table (yet to be created) of orders processed via the Date Key to allow KPI calculations.
Yes, you would normally create a calendar table, which is what your master date table is. The calendar table would only link to the other tables by the date. Looks like you've done that.
Now your problem is the loop, right? If the date on both tables has the same meaning, is the same date, then you only need it on one of the tables, and the loop is resolved that way. If the two dates have a DIFFERENT meaning, such as, say, a "shipped date" vs. "received date", you would typically create TWO calendars, and resolve the loop that way.
There are ways to handle multiple dates on multiple tables all with the same master calendar, but they are more complicated and actually answer different kinds of questions than the multiple-calendar solution. So what you need depends on what kinds of questions you want to ask of your data model. Multiple calendars are probably far more common. I'd suggest starting there.
Oh, and I wouldn't build the calendar in Excel. You're a QlikView programmer now, so program it in QlikView. I personally turned my calendar into a QVD, and just load the fields in the user applications. That helps standardize formatting and the like. Here's my file that creates the calendar. Mine references a database, but that's only to establish some fields you don't care about involving when we purge data from the live databases. In any case, it's just a reference, not something you'd copy whole and entire. Our week number, for instance, is neither the ISO nor the US standard.
masterCalendar.qvw 327.5 K
I can uderstand that, but how would I handle the following simplistic scenario.
I am in a warehouse scenario and I am creating a dashboard to calculate some efficiency KPIs.
I import timesheets by date and department - and create a calendar in Qlikview for selecting by various fields as above.
I also import inbound (import) and outbound (export) jobs - which also each have a date - and I can happily create a separate calendar for each one.
But now I want to calculate the cubic meters processed by each department for each principle on each date. I then want to work out the KPIs by dividing the CMB processed on a date by the hours worked on that date.
How do I link the tables to allow the calculation? Or do I just do a formula along the lines of:
By the way, both inbound and outbound volumes will get calculated using a Product table which is the same for both Inbound and Outbound jobs. By the same calendar logic, should I create a separate Inbound Product catalogue and a separate Outbound Product Catalogue - even though they are identical to avoid loops?
Several times people have told me to try and design a star configuration. Can you explain what that really means?
OK, so you have three different date fields on either two or three different tables:
And you want to, say, select 2011-5-16, and then be able to calculate everything inbound, outbound and worked on that date? In that case, you probably don't want the separate calendars I suggested, but rather a single calendar. I explain how to create a single calendar for multiple fields on multiple tables in this thread:
Unfortunately, the link to the final solution is no longer working, but the explanation should still be relevant. I should put together an example of my own some day, I suppose. Once you have your dates linked in the data model, you should be able to select a single value for your "Date" field, and then just do this:
As for a star configuration:
Though my own applications tend to more resemble a snowflake configuration: