Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
samuelt1
New Contributor

Associating Tables?

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.

5 Replies
Employee
Employee

Re: Associating Tables?

Hi Samuel,

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

Should work

samuelt1
New Contributor

Re: Associating Tables?

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!

datagrrl
Contributor III

Re: Associating Tables?

Is there a reason for this repeat of the business group in both tables? This doesn't make sense to me data model wise. I feel like this would make more sense if you had a vehicle dimension and maybe an attribute that said approved?

samuelt1
New Contributor

Re: Associating Tables?

The information from Toll Route Billing Information is a bill for usage that is provided to me every month.

The Approved Vehicle is a list of vehicles that have been previously approved and helps me identify and/or verify whether the usage of a toll was approved against the bill.

datagrrl
Contributor III

Re: Associating Tables?

Are plates approved by department? Meaning, does department matter, or is the key just the plate number?