Hi I am new to Qlikview…
We are currently struggling to aggregate calculated scores (from calculated values) up dimensionally.
We are producing a balanced scorecard where we have a number of metrics, each assigned a score (1 to 5) based on its value.
To produce the balanced scorecard, we need to aggregate the individual scores for metrics to produce an overall score for the domain (group of metrics). Each metric within the domain has its own weighting for aggregating the domain score.
We ALSO then need to aggregate multiple domain scores (again each domain has its own weighting) to formulate an over-arching score for the entire scorecard.
The scores and aggregates need to be calculated within the scorecard as the user will be selecting from multiple dimensions (Site, Department etc...).
These are the calculation stages:
- Numerator divided by Denominator = VALUE
- VALUE is mapped to predefined scoring criteria = SCORE
- SCORE is multiplied by predefined domain weightings and aggregated = DOMAIN SCORE
- SCORE is multiplied by predefined overall weightings and aggregated = OVERALL SCORE
The problem that I have encountered is that when aggregating dimensionally all of the Numerators and Denominators are summed (Stage 1), leading to unusable aggregations.
Only solution that can be found is to calculate stage 1,2,3 together for each metric then sum the results to get domain scores. This is a huge IF statement for just one metric, let alone the sixty-plus we have.
Any ideas would be greatly appreciated to make this less long winded – attached is an example (just 2 metrics)!
Cheers
