Total does not add up

[jcharles]

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

[simenkg · Accepted solution]

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

[jcharles]

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?

[jcharles]

Thank you - This one worked

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

[simenkg]

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

[#6]

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%.