In Pivot: different totals when collapsed or expanded

[dana]

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 🙂

[lennart_mo]

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.

[dana]

Hi,
I attached an Excel to the post.

Thank you!

[marcus_sommer]

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.

[dana]

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

[marcus_sommer · Accepted solution]

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

[dana]

Indeed, using only that expression resolved the issue:

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

Thanks!

[lennart_mo]

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?

[dana]

Hi,

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

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