topic Re: New user - help with aggregations not totaling correctly in QlikView

New user - help with aggregations not totaling correctly

KatherinePA — Thu, 21 Mar 2024 17:45:11 GMT

Hello,

Apologies if this is a basic question - I am new to Qlik!

I am having trouble with a ratio of two totals in QlikView.

- The total in A is just SUM(Field1).

- The total in B is just SUM(Field2)

- The ratio should be the sum of A divided by the sum of B, or 0.374

- Instead, the ratio is displaying as .416

- How can I get .374 to display in the total column?

Re: New user - help with aggregations not totaling correctly

Nicolae_Alecu — Thu, 21 Mar 2024 21:09:28 GMT

Hello,

Used formulas are in table header.

See my output below:

Re: New user - help with aggregations not totaling correctly

KatherinePA — Fri, 22 Mar 2024 11:49:15 GMT

Thanks so much for taking a look at this. If it worked like it is working for you, that would be wonderful! How do I get Qlik to do this? Is it a setting, or do I need to use AGGR expressions or something?

Here is another example of the output I get from Qlik. The [A]/[B] expression is correct for everything except the Total. The Total is where I need it to be correct. I need it to take a straight sum of A and divide it by a straight sum of B. It is giving me 100.6 when the correct answer is 82.6.

Group	A	B	[A]/[B]	Correct
	705,901,957.5	854,638,973.1	1.006	0.825965
A	339,898,625.1	455,638,791.8	0.746	0.745983
B	103,553,242.0	167,508,204.6	0.618	0.618198
C	31,839,473.4	86,839,151.2	0.367	0.366649
D	716,750.2	1,431,467.2	0.501	0.50071
E	76,785,342.9	80,865,892.2	0.950	0.949539
F	24.9	912.6	0.027	0.027251
G	153,108,499.0	62,354,553.4	2.455	2.45545

Re: New user - help with aggregations not totaling correctly

Nicolae_Alecu — Fri, 22 Mar 2024 12:22:37 GMT

Can you share used formulas?

Re: New user - help with aggregations not totaling correctly

marcus_sommer — Fri, 22 Mar 2024 13:55:11 GMT

As far as no simple sum() or count() are used else any kinds of rates are calculated and/or any conditions are included you may need an aggr() to get the wanted results, for example:

avg(aggr(sum(X)/sum(Y), DimX, DimY))

which returns the average of the row-results. But against your example data it won't return 0.82 else 0.81 which just showed that there is a different weight between the row-values. Beside this could any data-anomalies have an impact - means any kind of missing values or NULL within the data respectively the associations of them.

Before going further at this point take a look on the calculation-mode within the object-properties and try the different options. Further take also a look on your data-model if there are any circular loops or synthetic keys which mean that the data-model is invalide.

Re: New user - help with aggregations not totaling correctly

Nagaraju_KCS — Fri, 22 Mar 2024 14:23:28 GMT

Try this

SUM(Aggr(SUM(A)/SUM(B), Group))

Re: New user - help with aggregations not totaling correctly

KatherinePA — Fri, 22 Mar 2024 14:59:11 GMT

Thanks so much to everyone for looking at this.

The formulas are:

sum(Claim_Paid) * sum(ELIG_PART_MBR_MONTH_CNT) for A

and

sum(RECVD_GROSS_REV_AMT) * sum(ELIG_PART_MBR_MONTH_CNT)

for B.

The totals work properly for each group A, B, C, etc., but when they are aggregated they aren't correct

Group	A	B	[A/B]	With Aggr
	317,033,343.3	846,818,258.8	0.416	233.0%
A	173,272,011.8	455,638,791.8	0.380	38.0%
B	48,851,200.2	167,508,204.6	0.292	29.7%
C	17,769,545.9	86,839,151.2	0.205	19.3%
D	676,037.3	1,431,467.2	0.472	45.4%
E	27,521,443.2	73,045,177.9	0.377	38.0%
F	0.0	912.6	0.000	0.0%
G	48,943,104.8	62,354,553.4	0.785	62.6%

		should be	0.374381799