5 Replies Latest reply: Oct 5, 2018 10:12 AM by Randi Borys RSS

    Associating Tables?

    Samuel Trinh

      Hello, I am a new user to Qlik Sense:

       

      I have two workbooks that I am loading into a single Qlik Sense application.

       

      Example Below:

      1. Toll Route Billing Information-

      Department DescriptionPlate NumberPatternApproved for Toll Usage
      Stations GroupTY53-463Exit 1 to Exit 3Yes
      Facilities GroupPQ42-897Exit 2 to Exit 1Yes
      Safety GroupLT84-086Exit 3 to Exit 5Yes
      Safety GroupYR24-091Exit 4 to Exit 5Yes

       

      2. Approved Vehicles- Composed of a list of vehicles broken down into several business groups

      Example:

      Business GroupVehicle Plate Number
      Safety GroupLT84-086
      Safety GroupYR24-091
      Safety GroupPO83-781
      Facilities GroupPR02-451
      Operations GroupTT20-231
      Business Systems GroupCQ13-567
      Facilities GroupPQ42-897


      In the Billing Information file, I have created a helper column to identify whether a vehicle is approved for toll usage, and I have used the helper column as a part of my Qlik Sense Application to calculate the number of approved vehicles in use (per filtered time frame- as defined by user in the application)

       

      What I want to do is to use that count and calculate a percentage against the total number of approved vehicles in that group from the Approved Vehicles List. In this case it would be

       

      Vehicles from the Toll Route Billing Information File (2) divided by the total number of vehicles in the Approved Vehicles File (3)

      Result should be: 66%

       

      I have been able to write the first part of the but I am experiencing difficulty adapting my expression.

       

      What would be the best option for me to get this to work? If this has been resolved before please redirect me.

       

      Thanks everyone.

        • Re: Associating Tables?
          Lisa Phillips

          Hi Samuel,

           

          Count(distinct [Plate Number])/Count(distinct TOTAL [Vehicle Plate Number])

           

          Should work

          • Re: Associating Tables?
            Samuel Trinh

            Hi Lisa,

             

            The Count(distinct Total [Vehicle Plate Number]) only gives me the ability to count the total number of unique records in the Approved Vehicle List. (7)

             

            Is it possible to dynamically count the total number of vehicles of a particular selection set (as defined by the user)?

             

            So, if I were to connect a KPI (identifying % of Vehicles using Toll Roads) to a Filter pane,

             

            The return value for Facilities Group would be:

            1 (Number of Vehicles in Facilities Department Description / 2 (Total Number of Vehicles in Facilities Business Group) = 50%

             

            And if I toggled to another group, Safety Group

            2 (Number of Vehicles in Department Description / 3 (Total Number of Vehicles in Safety Business Group) = 66%

             

            Apologies if there was any previous confusion.

             

            Thanks!