topic Re: In Pivot: different totals when collapsed or expanded in App Development

In Pivot: different totals when collapsed or expanded

dana — Thu, 02 Apr 2026 14:38:08 GMT

Hi,

I see different total values for a year, when the tables is collapsed and expanded.

This is the collapsed view:

Expanded:

The expression used:

IF( Dimensionality()=3,
Sum(Duration)
/
Count(distinct ServiceCall)
,

Avg( Aggr(
Sum(Duration)
/
Count(distinct ServiceCall), Year,IssueType, Machine)))

Why does it happen, and what can be done to resolve this?

Thnx in advance 🙂

Re: In Pivot: different totals when collapsed or expanded

lennart_mo — Thu, 02 Apr 2026 13:55:55 GMT

Hi @dana,

could you maybe provide the expanded table as an excel file or a screenshot of all Years, Issue Types and Machines expanded? Since 2025 and 2026 are showing values different to the collapsed table as well.

Seeing the complete data might help identify the problem quicker.

Re: In Pivot: different totals when collapsed or expanded

dana — Thu, 02 Apr 2026 14:38:56 GMT

Hi,
I attached an Excel to the post.

Thank you!

Re: In Pivot: different totals when collapsed or expanded

marcus_sommer — Thu, 02 Apr 2026 15:00:21 GMT

Your calculations are logically different because sum() / count() is not equally to avg(). The solution will probably be to use a single calculation for this information - and maybe another chart for the stuff which needs a different one and/or removing the dimensionality() and aggr() branch completely.

Re: In Pivot: different totals when collapsed or expanded

dana — Thu, 02 Apr 2026 15:07:26 GMT

Hi Marcus,

The original chart does not have AGGR nor Dimensionality, but has the same issue.

I added them trying to resolve it.

Re MTTR (Mean time to repair): how would you suggest to calculate it?

Thanks

Re: In Pivot: different totals when collapsed or expanded

marcus_sommer — Thu, 02 Apr 2026 15:20:09 GMT

It depends on the wanted result respectively what expects the business to see in this special context. I could imagine that you need something like the avg(aggr(sum()/count(), Dim1, Dim2)) for the entire chart and rather unlikely any kind of mixed calculation.

Helpful would be to add also the sum() and count() as separate expressions to comprehend better which average-result has which background and is rather the better one. More background to the not mandatory trivial matter is here: Average – Which average? - Qlik Community - 1466654

Re: In Pivot: different totals when collapsed or expanded

dana — Thu, 02 Apr 2026 15:26:52 GMT

Indeed, using only that expression resolved the issue:

Avg( Aggr(
Sum(Duration)
/
Count(distinct ServiceCall), Year,IssueType, Machine)))

Thanks!

Re: In Pivot: different totals when collapsed or expanded

lennart_mo — Thu, 02 Apr 2026 15:43:36 GMT

Hi @dana,
I just looked at your data and though @marcus_sommer already provided you with a solution, I'm just curious to ask: Are there any NULL-Values for Machines or Issue Types you chose to not display in either of the Dimensions?

Re: In Pivot: different totals when collapsed or expanded

dana — Thu, 02 Apr 2026 18:01:24 GMT

Hi,

The expression includes flags so there is no need to suppress null dimension values:

{<Flag_InMachineMetrics = {1}, Flag_MonitoredMachine = {1}>}