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 theMazeEscapestable, as it holds the actual records of rodent attempts.
By creating a new fieldRodentID_Counterin theMazeEscapestable, the analyst ensures that each rodent’s attempt is captured. Using theCount (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,Ais the verified answer because it ensures that each rodent is counted once, based on their attempts in the MazeEscapes table.
