Using Qlik Sense (September 2017 edition), not very experienced with Qlik...
I have a table that contains, among other things, data like the following - I'll call it "Events":
| Event ID | Event Type | Event Start Date (yyyy-mm-dd) | Event End Date (yyyy-mm-dd) |
|---|
| 1 | Type 1 | 2018-01-25 | 2018-01-27 |
| 2 | Type 1 | 2018-01-26 | 2018-01-28 |
| 3 | Type 2 | 2018-01-24 | 2018-01-29 |
| 4 | Type 3 | 2018-01-31 | 2018-02-03 |
| 5 | Type 2 | 2018-01-29 | 2018-01-29 |
You get the idea.
I am currently displaying the following graph, called "Events by Type", built directly off of Events.
| Event Type | Event Occurrences (count of Events rows by type) |
|---|
| Type 1 | 2 |
| Type 2 | 2 |
| Type 3 | 1 |
I need to display a graph that acts as a daily counter, if you will - i.e. count up the total number of events per day. I wrote a bit of SQL to produce the following table out of the table above.
This table shows the total number of events happening by type each day. There is one occurrence of Type 2 on 2018-01-24, two occurences of Type 1 on 2018-01-26 (from event 1 and event 2), and so on. Call this "Events by Day and Type".
| Date | Event Type | Event count (by type) |
|---|
| 2018-01-24 | Type 2 | 1 |
| 2018-01-25 | Type 1 | 1 |
| 2018-01-25 | Type 2 | 1 |
| 2018-01-26 | Type 1 | 2 |
| 2018-01-26 | Type 2 | 1 |
| 2018-01-27 | Type 1 | 2 |
| 2018-01-27 | Type 2 | 1 |
| 2018-01-28 | Type 1 | 1 |
| 2018-01-28 | Type 2 | 1 |
| 2018-01-29 | Type 2 | 2 |
| 2018-01-31 | Type 3 | 1 |
| 2018-02-01 | Type 3 | 1 |
| 2018-02-02 | Type 3 | 1 |
| 2018-02-03 | Type 3 | 1 |
The following shows the sum of "event days" grouped by type - let's call it "Event Days by Type":
| Event Type | Event Days |
|---|
| Type 1 | 6 |
| Type 2 | 7 |
| Type 3 | 4 |
The following shows the sum of "event days" grouped by the date (so not caring about the type) - let's call it "Total Events by Day":
| Date | Total Events |
|---|
| 2018-01-24 | 1 |
| 2018-01-25 | 2 |
| 2018-01-26 | 3 |
| 2018-01-27 | 3 |
| 2018-01-28 | 2 |
| 2018-01-29 | 2 |
| 2018-01-31 | 1 |
| 2018-02-01 | 1 |
| 2018-02-02 | 1 |
| 2018-02-03 | 1 |
The total number of event days altogether would be 17 days.
I also have a table that I'll call "Total Customers by Day" - similar to the "Total Events by Day" table.
| Date | Total Customers |
|---|
| 2018-01-24 | 5 |
| 2018-01-25 | 6 |
| 2018-01-26 | 5 |
| 2018-01-27 | 2 |
| 2018-01-28 | 8 |
| 2018-01-29 | 5 |
| 2018-01-31 | 3 |
| 2018-02-01 | 2 |
| 2018-02-02 | 3 |
| 2018-02-03 | 1 |
Total Events by Day would be something like cooking classes (which can be multi-day classes) being given at your venue that day, while Total Customers by Day shows all the customers who are teaching the classes (which can have multiple instructors, who are the customers here) at your venue on any given day.
My question is coming, I promise.
I need to create a dashboard that allows users to show a rate of events happening per total number of event days. The first graph has me showing a graph of the following table - let's call it the "Event Rate by Day and Type" table:
| Date | Type | Event Rate (event days/total customers for day) |
|---|
| 2018-01-24 | Type 2 | 1/5 |
| 2018-01-25 | Type 1 | 1/6 |
| 2018-01-25 | Type 2 | 1/6 |
| 2018-01-26 | Type 1 | 2/5 |
| 2018-01-26 | Type 2 | 1/5 |
| ... | | |
and so on. A "Total Event Rate by Day" would sum the event days over type like so:
| Date | Total Event Rate |
|---|
| ... | |
| 2018-01-26 | 3/5 |
| 2018-01-27 | 3/2 (e.g. 3 classes happening, you have 2 customers... some very busy customers) |
| ... | |
| 2018-02-02 | 1/3 |
Independently, these tables would all calculate the correct value in Qlik Sense and show nice graphs.
OK, now on to the question (finally): where I am having trouble is hooking up Events by Type (from near the top of this post) to Event Rate by Day and Type. This looks like it has something to do with the fact that all the tables above are separate tables loaded into Qlik from my SQL data source (which resulted from the Events table not being set up in a way that was conducive for this sort of daily accounting).
One use case with all these tables is that I want to be able to click on a Type in Events by Type and see the corresponding Event Rate by Day and Type graph be filtered on the type selected in Events by Type. Because of other graphs in this dashboard (that aren't quite relevant to this question but needed for the business), I can't simply let Qlik link the tables by Type, and a result, it seems to me that the only way Qlik can hook up Events by Type to Event Rate by Day and Type would be through the Event ID somehow. Why? Because neither Events nor Events by Type (the top two, original tables/graphs) cares about these running dates, which is needed for all the other tables I showed.
All of that means I added the Event ID to my Events by Day and Type (the first "running count" table I imported into Qlik), and voila, now Event Rate by Day and Type filters on the type I select in Events by Type!
...Except that I actually need to show this information on a monthly basis. No biggie, I can just switch the dimension to the YearMonth version provided by Qlik's autocalendar thing. The totals should be (different header column to indicate that I did not import them, just showing the real totals😞
| Year | Month | Total Events by Year/Month | Total Customers by Year/Month |
|---|
| 2018 | 1 | 14 | 34 |
| 2018 | 2 | 3 | 6 |
And if I introduce type, it should follow that the rate of events by type per month would be:
| Year | Month | Type | Total Events by Type by Year/Month | Event Rate (event/total customers) by Type per Month |
|---|
| 2018 | 1 | Type 1 | 6 | 6/34 |
| 2018 | 1 | Type 2 | 7 | 7/34 |
| 2018 | 1 | Type 3 | 1 | 1/34 |
| 2018 | 2 | Type 3 | 3 | 3/6 |
However, when I make a table in Qlik using the following dimension (please ignore any syntax errors, I assure you I don't do this in my real graphs]:
=[Date.autoCalendar.YearMonth]
The measure
=Sum([Total Customers by Day].[Total Customers])
yields the following table:
| Year | Month | Total Customer by Year/Month |
|---|
| 2018 | 1 | 40 |
| 2018 | 2 | 40 |
In other words, the Sum([Total Customers]) summed up that data point without taking the Date, which is shared by all the running count tables I showed above, into account. Naturally, this ruins my Event Rate by Type per Month value I am looking for. What could cause this to happen?
OK, I know this is a ridiculously long post, but the problem I have at hand is what it is. Any feedback is welcome, including the "your data model is wrong" feedback.
.png "Luminary Alumni"){kind=icon}