Qlik Community

Qlik Sense App Development

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

jooeunvps
New Contributor

Linking a "counting" table to table with actual data

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 IDEvent TypeEvent Start Date (yyyy-mm-dd)Event End Date (yyyy-mm-dd)
1Type 12018-01-252018-01-27
2Type 12018-01-262018-01-28
3Type 22018-01-242018-01-29
4Type 32018-01-312018-02-03
5Type 22018-01-292018-01-29

You get the idea.

I am currently displaying the following graph, called "Events by Type", built directly off of Events.

Event TypeEvent Occurrences (count of Events rows by type)
Type 12
Type 22
Type 31

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".

DateEvent TypeEvent count (by type)
2018-01-24Type 21
2018-01-25Type 11
2018-01-25Type 21
2018-01-26Type 12
2018-01-26Type 21
2018-01-27Type 12
2018-01-27Type 21
2018-01-28Type 11
2018-01-28Type 21
2018-01-29Type 22
2018-01-31Type 31
2018-02-01Type 31
2018-02-02Type 31
2018-02-03Type 31

The following shows the sum of "event days" grouped by type - let's call it "Event Days by Type":

Event TypeEvent Days
Type 16
Type 27
Type 34

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":

DateTotal Events
2018-01-241
2018-01-252
2018-01-263
2018-01-273
2018-01-282
2018-01-292
2018-01-311
2018-02-011
2018-02-021
2018-02-031

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.

DateTotal Customers
2018-01-245
2018-01-256
2018-01-265
2018-01-272
2018-01-288
2018-01-295
2018-01-313
2018-02-012
2018-02-023
2018-02-031

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:

DateTypeEvent Rate (event days/total customers for day)
2018-01-24Type 21/5
2018-01-25Type 11/6
2018-01-25Type 21/6
2018-01-26Type 12/5
2018-01-26Type 21/5
...

and so on. A "Total Event Rate by Day" would sum the event days over type like so:

DateTotal Event Rate
...
2018-01-263/5
2018-01-273/2 (e.g. 3 classes happening, you have 2 customers... some very busy customers)
...
2018-02-021/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):


YearMonthTotal Events by Year/MonthTotal Customers by Year/Month
201811434
2018236


And if I introduce type, it should follow that the rate of events by type per month would be:

YearMonthTypeTotal Events by Type by Year/MonthEvent Rate (event/total customers) by Type per Month
20181Type 166/34
20181Type 277/34
20181Type 311/34
20182Type 333/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:

YearMonthTotal Customer by Year/Month
2018140
2018240


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.

1 Solution

Accepted Solutions

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

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

2 Replies
juraj_misina
Valued Contributor

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

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

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

Community Browser