topic Re: Pivot Table: Totals Not Correct in Calculations for Conditional Formatting in Data Movement & Streaming

Pivot Table: Totals Not Correct in Calculations for Conditional Formatting

Crichter141 — Wed, 17 Jun 2026 21:56:46 GMT

Conditional Formatting Benchmark Mismatch with Column Dimensions and Label Filtering

Description:

I have a pivot table with:

Rows: Product labels (8 total: P1–P8)
Columns: Months (calculated dimension: ='Month ' & Month_Num)
Values: Turn Rate metric

The Problem:

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?

Re: Pivot Table: Totals Not Correct in Calculations for Conditional Formatting

PrashantSangle — Thu, 18 Jun 2026 05:07:33 GMT

As per Qlik Help Page, this is how TOTAL work

"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?

Try with Sum(Aggr(yourexpression, dim1, dim2))

Ref - https://help.qlik.com/en-US/sense/May2026/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/define-aggregation-scope.htm

https://community.qlik.com/t5/Design/What-does-the-TOTAL-qualifier-do/ba-p/1472990

Regards,

Prashant Sangle

Re: Pivot Table: Totals Not Correct in Calculations for Conditional Formatting

marcus_sommer — Thu, 18 Jun 2026 06:18:32 GMT

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:

if(dimensionality() = 0, Expr1, Expr2)

to calculate per dimension-level differently.

Re: Pivot Table: Totals Not Correct in Calculations for Conditional Formatting

Crichter141 — Wed, 24 Jun 2026 16:34:22 GMT

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!