topic Re: Issues with sum function in QlikView

Issues with sum function

Sun, 28 Sep 2014 11:11:50 GMT

Hey all!

So I'm having a really inoing issue... The sum does not seem to work in pivot tables or in the straight tables, and this is not for all dimensions just some, and I'm out of ideas...

So i simply used the function =sum([field]) and it should work right, but it doesn't. The data is uploaded from an excel spread sheet and the sum is correct when you extract fields to a list box but not in the pivot table. So the sum in the list box is 227556 + 339631 + 215615 = 782 802 HOWEVER.... in the pivot table the sum is 1917180

sum([Invoiced Value EUR])

Any ideas?

Thanks guys!

Re: Issues with sum function

ashfaq_haseeb — Sun, 28 Sep 2014 11:16:56 GMT

Hi,

Try like this

aggr(sum([Invoiced Value EUR]),[Site Name],[Supplier Name])

Regards

ASHFAQ

Re: Issues with sum function

anbu1984 — Sun, 28 Sep 2014 11:50:11 GMT

Can you provide sample input

Re: Issues with sum function

Sun, 28 Sep 2014 11:51:58 GMT

Hi,

Thank you for your answer unfortunately it did not change the out put... still getting the same numbers.

BR,

Hannah

Re: Issues with sum function

ashfaq_haseeb — Sun, 28 Sep 2014 11:52:52 GMT

Hi,

Can you upload sample application.

Regards

ASHFAQ

Re: Issues with sum function

veidlburkhard — Sun, 28 Sep 2014 12:23:50 GMT

Hi Hannah,

the number 1917180,11 looks like being the TOTAL of all suppliers regardless of your selection!?!

Need your sample data for further help...

Regards

Burkhard

Re: Issues with sum function

afurtado — Sun, 28 Sep 2014 13:49:56 GMT

Hi,

like ashfaq_haseeb wrote but maybe using distinct

sum( aggr(sum( DISTINCT [Invoiced Value EUR]),[Site Name],[Supplier Name]))

Best,

Alessandro Furtado

Re: Issues with sum function

MarcoWedel — Sun, 28 Sep 2014 15:03:40 GMT

Hi,

you have multiple records containing the same Invoiced Value:

1917180.1149425 = 3 * 227556.55172414 + 3 * 339631.6091954 + 215615.63218391

so the Sum(Distinct [Invoiced Value]) might work if there are always distinct values per site name / supplier name combination, but can this be guaranteed?

Look into your data model for the reasin of those records occuring multiple times

hope this helps

regards

Marco

Re: Issues with sum function

Mon, 29 Sep 2014 06:32:37 GMT

Thank you Alessandro! That fixed the issue, do you recommend to do this in all tables?

Best regards,

Hannah

Re: Issues with sum function

ashfaq_haseeb — Mon, 29 Sep 2014 06:33:44 GMT

Yes.

Regards

ASHFAQ