2 Replies Latest reply: Feb 22, 2018 11:33 PM by Rob Wunderlich RSS

    Linking a "counting" table to table with actual data

    Jooeun Lee

      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.

        • Re: Linking a "counting" table to table with actual data
          Juraj Misina

          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
            Rob Wunderlich

            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