topic Re: Total does not add up in QlikView

Total does not add up

jcharles — Fri, 18 Jul 2014 13:29:31 GMT

Good day

I have a chart where every column's total adds up correctly except for the last column which should show the total as R384.88 but it shows R389.98. I don't know why. Can someone help please.

KEYWORD	NO	AMOUNT	AMT PER NO	NO OF LEADS	COST PER LEAD
TOTAL	163	R1,767,380.59	R10,842.83	4,592	R389.98
Sun	45	R627,008.68	R13,933.53	1,852	R338.56
Move	14	R297,787.00	R21,270.50	554	R537.52
Kaap	35	R213,786.72	R6,108.19	642	R333.00
Family	6	R205,650.00	R34,275.00	379	R542.61
krag	6	R111,055.50	R18,509.25	198	R560.89
Goal	6	R90,732.60	R15,122.10	255	R355.81
Iso	18	R80,598.01	R4,477.67	180	R447.77
Ilanga	17	R69,186.35	R4,069.79	165	R419.31
Value	3	R38,181.15	R12,727.05	102	R374.33
Sonto	12	R24,771.86	R2,064.32	46	R538.52
Nurse	1	R8,622.72	R8,622.72	11	R783.88
Bona	0	R0.00	-	54	R0.00
Drum	0	R0.00	-	38	R0.00
Rights	0	R0.00	-	38	R0.00
You	0	R0.00	-	38	R0.00
New	0	R0.00	-	14	R0.00
World	0	R0.00	-	2	R0.00
Love	0	R0.00	-	2	R0.00
Mthatha	0	R0.00	-	2	R0.00
Khosi	0	R0.00	-	11	R0.00
Kick	0	R0.00	-	1	R0.00
City	0	R0.00	-	1	R0.00
Times	0	R0.00	-	1	R0.00
Day	0	R0.00	-	6	R0.00

Re: Total does not add up

Anonymous — Fri, 18 Jul 2014 13:31:57 GMT

Check in the properties whether you have used "Expression Totals" or Sum Totals.

Re: Total does not add up

jcharles — Fri, 18 Jul 2014 13:34:40 GMT

I'm using the expression total. I cannot change it to sum of rows total because then it gives me R5 232.20 which is also incorrect.

Re: Total does not add up

gmoraleswit — Fri, 18 Jul 2014 13:35:12 GMT

Check this: http://community.qlik.com/blogs/qlikviewdesignblog/2014/06/23/subtotals

Re: Total does not add up

Anonymous — Fri, 18 Jul 2014 13:36:37 GMT

Send me your logic to get R384.88

Re: Total does not add up

simenkg — Fri, 18 Jul 2014 13:39:16 GMT

What does your expression look like?

Try the expression:

[AMOUNT] / [NO OF LEAD]

Re: Total does not add up

richard_pearce6 — Fri, 18 Jul 2014 13:56:22 GMT

You could try an aggr()

Avg ( Aggr ( <Your Expression> , KEYWORD ) )

Richard

Re: Total does not add up

jcharles — Fri, 18 Jul 2014 14:18:12 GMT

R1767380.59 / 4592 = R384.88

sum(AMOUNT) / COUNT(DISTINCT(LEADNO))

Re: Total does not add up

jcharles — Fri, 18 Jul 2014 14:19:41 GMT

I'm using :

sum(AMOUNT) / COUNT(DISTINCT(LEADNO))

Re: Total does not add up

Fri, 18 Jul 2014 14:22:27 GMT

If you have tried all of the above it may mean that you have a disconnect in your data somewhere.

Re: Total does not add up

simenkg — Fri, 18 Jul 2014 14:25:18 GMT

And in the NO OF LEADS column:

Are you using COUNT(DISTINCT LEADNO) or COUNT(LEADNO)?

This would explain the wrong total if there are some LEADNO that exists for more than one KEYWORD. It would then only be counted once in the total.

If sum(AMOUNT) / COUNT( LEADNO) does not work, try

sum(AMOUNT) / sum(aggr(Count(Distinct LEADNO),KEYWORD)))

Regards
SKG

Re: Total does not add up

jcharles — Fri, 18 Jul 2014 14:25:41 GMT

Thanks - this must be true because if I filter per month I get to a total of 4351 leads and not 4592 leads. I think this is the case but how do I fix it?

Re: Total does not add up

jcharles — Fri, 18 Jul 2014 14:31:12 GMT

Thank you - This one worked

sum(AMOUNT) / sum(aggr(Count(Distinct LEADNO),KEYWORD)))

Re: Total does not add up

simenkg — Fri, 18 Jul 2014 14:32:21 GMT

Because you have a field and an expression with the same name, the expression
[AMOUNT] / [NO OF LEADS] will not work.

What I'm trying to do is tell it to take the values for those expressions and dividing them by each other. It is "the same" as using

Column(2) / Column(4)

however, using this syntax will not let you add columns before the expression.

You can rename the Expression AMOUNT into TOTAL AMOUNT and use the expression:

[TOTAL AMOUNT] / [NO OF LEADS]

Regards
SKG

Re: Total does not add up

Fri, 18 Jul 2014 14:32:27 GMT

Hi Joyce,

You have to look at the link between the different tables in your data structure and make sure that all links up correctly.

Right click on the links in your tables and look at the Subset Ratio's between the tables as well as the Information Densities.

The subset ratios need to be very close to 100%.