Associating Tables?

[Anonymous]

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 Description	Plate Number	Pattern	Approved for Toll Usage
Stations Group	TY53-463	Exit 1 to Exit 3	Yes
Facilities Group	PQ42-897	Exit 2 to Exit 1	Yes
Safety Group	LT84-086	Exit 3 to Exit 5	Yes
Safety Group	YR24-091	Exit 4 to Exit 5	Yes

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

Example:

Business Group	Vehicle Plate Number
Safety Group	LT84-086
Safety Group	YR24-091
Safety Group	PO83-781
Facilities Group	PR02-451
Operations Group	TT20-231
Business Systems Group	CQ13-567
Facilities Group	PQ42-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.

[Lisa_P]

Hi Samuel,

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

Should work

[Anonymous]

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]

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?

[Anonymous]

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]

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