My conditional formatting compares each product's turn rate to the Totals row (benchmark). The Totals row displays 124% for MONTH 9, but my conditional formatting benchmark calculates to 117% for the same month—a 7% discrepancy.
Root Cause:
My conditional formatting includes a filter that restricts the benchmark calculation to 6 of the 8 products. Two products (P7, P8) are intentionally excluded from coloring but are included in the Totals row. The benchmark appears to calculate on only the 6 products while the Totals row includes all 8, causing the mismatch.
The Ask:
Why is my conditional formatting benchmark calculating to 117% instead of 124%? Is this a limitation of the TOTAL qualifier with calculated column dimensions, or is there a way to make the benchmark match the Totals row value?
If you use two different main-calculations you will get two different results but one of them might be extended to branch into n ones considering the specific context, for example with something like:
"The total qualifier may be used inside an aggregation in order to redefine the aggregation scope by disregarding the dimensional values. The aggregation will instead be performed on all possible field values."
You might need to find another way to evaluate, what you are looking for?
Great dreamer's dreams never fulfilled, they are always transcended. Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
If you use two different main-calculations you will get two different results but one of them might be extended to branch into n ones considering the specific context, for example with something like:
So the trick was to create two new benchmarks in the load script and mapping them out to the correct granularity. I can't claim to be an expert, but Marcus solution put us on the right path, so thank you, MVP!
