Aggregating a KPI across different dimensions/levels
Hello. I have an aggregation issue that I'm hoping a few extra pairs of fresh eyes will help solve. I've attached a simple app and a spreadsheet, which contains the data in the app and also the expected aggregations on the right that I'm trying to replicate in the Qlik Sense app.
We have Levels 1-4 that represent an unbalanced location hierarchy, year, month and the two fields that are to be used in our KPI calculation, both of which are simple counts of 1) near misses/observations (incidents) and 2) number of employees. The KPI is derived by dividing 1 by 2. e.g. [Sum of Near Misses and Observations]/[Sum of Full Time Employees].
The time-based KPI is working fine (KPI time in the app) and matches the expected output in the Excel sheet with the following aggregation:
We're stuck with the site-based aggregation, though. As you'll see from the spreadsheet the top level figure [L15] is meant to match the time-based aggregation [L5] (as you'd expect with no selections) but we're struggling to implement the site-based aggregation as the calculation is slightly different. Rather than being able to perform a simple aggregation we need to factor in the level of the site hierarchy to get our totals.
e.g at the lowest level (3 or 4) it just needs to sum up the individual site totals, at levels 1 and 2 it needs to aggregate across the sites within those site groups.
Although I've left a calculation that doesn't work in the app (switching out Month for Site from the above aggregation) rest assured we have tried all manner of different aggregations based on our web searching without success.
Any advice would be appreciated. Hopefully I've explained the problem well enough.