topic Re: Totals not correct in QlikView

Totals not correct

FrankC — Sat, 16 Nov 2024 18:12:26 GMT

The totals are not matching the sum of the rows. I have tried using AGGR, but still not working. What I am doing wrong?

Here is the expression:

If(Sum([Budget OccSF]) > Sum(TotalSQF),
Sum(Aggr(Sum(TotalSQF),Facility,OType)),
Sum(Aggr(Sum([Budget OccSF]),Facility,OType))
)

Here is the result table. The total for Owner is not matching the sum of the rows

	Store	Yesterday	Yesterday Budget
Managed	5828	89.82%	2232479.93
Managed	6164	97.17%	1794356.72
Managed	6165	92.67%	1431386.71
Managed	6166	95.71%	2156914.57
Managed	6167	94.83%	1949440.98
Managed	6168	78.49%	2343843.67
Managed	6169	94.24%	1130630.75
Managed	6276	97.19%	2227164.3
Managed	6314	95.07%	1823194.16
Managed	6345	91.31%	1782223.81
Managed	6481	93.13%	2252497.44
Managed	Total	92.06%	21124133.04
Owned	745	95.97%	1456016.45
Owned	800	96.15%	2304389.92
Owned	802	89.87%	2153505.37
Owned	912	89.58%	2465275
Owned	Total	92.64%	8993565	8379186.74
		92.22%	1966887.99

Re: Totals not correct

Nicole-Smith — Thu, 20 Aug 2020 15:59:11 GMT

On the measure, you should be able to change the Totals function from "Auto" to "Sum" where it will do sum of rows.

Re: Totals not correct

FrankC — Thu, 20 Aug 2020 17:16:40 GMT

It was already set to sum

Re: Totals not correct

marcus_sommer — Fri, 21 Aug 2020 09:06:37 GMT

Usually needs the aggr-wrapping to be on the outside of the expression to ensure that the total is equally to the sum of rows. Therefore I think your expression should be rather look like:

Sum(Aggr(
If(Sum([Budget OccSF]) > Sum(TotalSQF), Sum(TotalSQF), Sum([Budget OccSF]),
Facility,OType))

- Marcus