topic Sum not calculating correctly in QlikView

Sum not calculating correctly

ivandrago — Wed, 23 Feb 2011 00:17:38 GMT

Hi,

I am trying to do a sum calculation but the figure is not showing to what it should be, I want it to be £1, 612, 536.42 but it showing as £1, 414, 607.77, I tried to do the formula in the text box underneath "Total RRP CORRECT" but it does not seem to work? Also will I need to change the different charts with the new formula as they are using seperate dimension when clicking on the relevant buttons on the side?

Thanks

Sum not calculating correctly

ivandrago — Wed, 23 Feb 2011 09:52:50 GMT

Anyone any ideas?

AW:Re: Sum not calculating correctly

Wed, 23 Feb 2011 10:03:05 GMT

Hi Ivan,

I wonder about the DISTINCT in your formula. What is the reason for it? A Sum(Distinct...) will only sum up a certain value once and that may be the reason why you see a different total.

Example:

Cust A 5

Cust B 5

You would expect a total of 10, but as they have both the same value, it will be summed only once and return a total of 5. I hope that helps to give you the right idea...

cheers

Florian

Sum not calculating correctly

suniljain — Wed, 23 Feb 2011 10:20:40 GMT

Remove Distinct From Expression

Sum not calculating correctly

ivandrago — Wed, 23 Feb 2011 10:50:00 GMT

Hi,

I have sorted the formula now, but something wierd is happening! When I click on Person 131 it shows the following figures in the Straight table which are correct as :

Total RRP - £648,703.79

Total Mar - £38,257.82

Total Comm - £86.19

% Diff - 0.23%

But when I have no filter on, Person 131 is showing the incorrect figures as

Total RRP - £24,697.18

Total Mar - £564.18

Total Comm - £86.19

% Diff - 15.28%

Any ideas why it would do this?

AW:Re: Sum not calculating correctly

Wed, 23 Feb 2011 11:06:02 GMT

Hi Ivan,

this is because you need to have the Person's name in your aggregation field list as well. If you add it, the result will be correct:

Sum(Aggr(Distinct RRP,[Person Name], ID,[ID Line No]))

cheers

Florian

AW:Re: Sum not calculating correctly

ivandrago — Wed, 23 Feb 2011 11:48:05 GMT

Hi Florian,

I have added that now and seems tobe working!! Thanks! But the Total on the chart for Total RRP is showing as £15,927,116.17 ?

AW:Re: AW:Re: Sum not calculating correctly

Fri, 25 Feb 2011 20:13:52 GMT

Hi Ivan,

yes, summing up the sales reps makes 15m. The 1.6m comes from a DISTINCT on RRP and as explained before, that does not sum up double values. I saw some of the reps have the same result, but those won't be summed up as long as you have a DISTINCT with the value field.

cheers

Florian