How should the analyst construct the KPI?

Refer to the exhibit.

An app that will track experiments for rodents (e.g., rats and mice) that navigate mazes (labyrinths) is being developed. Individual rodents are catalogued in the Rodent table, while the Mazes table has metadata for the mazes. The MazeEscapes table holds a record of each attempt at a maze by a rodent. A business analyst needs to build a KPI that will allow users to see how many rodents have made at least one attempt at any maze.

How should the analyst construct the KPI?
A . • Create RodentlD AS RodentID_Counter in the MazeEscapes table.
• Use Count (Distinct RodentlD Counter) as the KPI expression.
B . • Create 1 AS RodentID_Counter in the Rodent table.
• Use Sum (RodentlD Counter) as the KPI expression.
C . Create 1 AS RodentID_Counter in the MazeEscapes table.
• Use Sum (RodentlD Counter) as the KPI expression.
D . • Create RodentlD AS RodentlD Counter in the Rodent table.
• Use Count (Distinct RodentID Counter) as the KPI expression.

View Answer

Answer: A

Explanation:

In this case, we need to count how many unique rodents have made at least one attempt at any maze. Since the relationship between the Rodent table and the MazeEscapes table is a one-to-many relationship (each rodent may attempt several mazes), the best approach is to base the calculation on the MazeEscapes table, as it holds the actual records of rodent attempts.

By creating a new field RodentID_Counter in the MazeEscapes table, the analyst ensures that each rodent’s attempt is captured. Using the Count (Distinct RodentID_Counter) as the KPI expression then accurately counts each unique rodent that has made at least one attempt, without counting multiple attempts by the same rodent.

Key Concepts:

Distinct Count: The distinct count ensures that rodents who have made multiple maze attempts are only counted once.

MazeEscapes Table: This table holds the records of each attempt, making it the correct source for this KPI.

Why the Other Options Are Less Suitable:

B. Sum (RodentID_Counter) in the Rodent table: This would incorrectly sum the number of rodents rather than counting unique attempts, leading to inaccurate results.

C. Sum (RodentID_Counter) in the MazeEscapes table: Summing the counter would not account for the distinct rodents, leading to incorrect counting.

D. RodentID_Counter in the Rodent table: This would not correctly reflect the rodents that have made an attempt, as not every rodent in the Rodent table may have attempted a maze. References for Qlik Sense Business Analyst:

Distinct Count in KPI: It is best practice to use Count(Distinct …) when working with KPIs that require unique counts, such as counting distinct rodents in this scenario.

Thus, A is the verified answer because it ensures that each rodent is counted once, based on their attempts in the MazeEscapes table.