Linking a "counting" table to table with actual data

Anonymous — Thu, 22 Feb 2018 20:41:09 GMT

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.

Re: Linking a "counting" table to table with actual data

juraj_misina — Thu, 22 Feb 2018 22:35:48 GMT

Hi,

can you maybe share an app with sample data? It's quite hard to follow and assess from this long post.

I do have two ideas though:

1. "Total" is a keyword in Qlik doing exactly what happened to you: summing up stuff ignoring chart dimenions. Although you wrap your column labels in [] I'd still try to rename those fields which contain "Total", just in case that there's a bug in Qlik Sense.

2. I can imagine that everything you mention here is achievable with a simple data model containing a fact table, a "date bridge" linking date intervals from fact table to discrete days and a calendar table, plus perhaps a few dimension tables. So no need to load separate fact tables where you can run into troubles with complex associations, which is probably what happened here.

Juraj

Re: Linking a "counting" table to table with actual data

rwunderlich — Fri, 23 Feb 2018 04:33:39 GMT

I think you are taking the wrong approach by pre calculating anything. You'll want to learn about the IntervalMatch script prefix, which is the most flexible way to approach this. IntervalMatch connects your fact into each date in the range which allows you to calc sums on the fly at any dimensional level. Read up on IntervalMatch in the Help.

Download the QlikSense version of this example:

Qlikview Cookbook: Count Days In A Transaction Using Intervalmatch http://qlikviewcookbook.com/recipes/download-info/count-days-in-a-transaction-using-intervalmatch/

I've also applied the same pattern to your data and attached here. I had to fake in some customer counts because you didn't have them in the first table. I was assuming customers stays constant throughout all days of an event.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

topic Re: Linking a "counting" table to table with actual data in App Development

Linking a "counting" table to table with actual data

Re: Linking a "counting" table to table with actual data

Re: Linking a "counting" table to table with actual data