topic Not getting the correct sum figure in QlikView

Not getting the correct sum figure

ivandrago — Wed, 06 Oct 2010 23:32:17 GMT

Hi,

For the expression COP on the Summary chart the SUM Function does not seem to be returning the correct results?
I want the value 1672.80 (this is worked out if you sum the cost field on the Detail table) but I am getting 314.3703 any ideas why this is?

Thanks

Not getting the correct sum figure

syed_muzammil — Wed, 06 Oct 2010 23:56:23 GMT

Hi,

The sum function is correct. If you create a simple Table box with fields Part Group and cost then you can see that the Sum(Cost) is correct. There maybe some other problem.

Regards,

Syed.

Not getting the correct sum figure

disqr_rm — Thu, 07 Oct 2010 00:05:09 GMT

It's because you have multiple records for the same PartPK in Stock table compare to the Parts table.

For example for for PartPK = tree_COR36L8681 you have 7 records in Stock table and 1 record in Parts table. The chart is considering only 1 occurrence of this value.

It seems, to me, that you need to multiple the cost with some kind of Quantity field from Stock. So it would be that you are getting the cost from Parts table and then multiplying with Quantity from Stock table, so it will give you the value you need.

I tried with multiplying with "Quantity On Hand Usable" and it seems to be giving pretty close results to what you are looking for. Try changing expression to SUM(Cost * [Quantity On Hand Usable]) and you will see the results. Make sure to pick the right field for this quantity multiplication.

Hope this helps you.

Not getting the correct sum figure

llauses243 — Thu, 07 Oct 2010 01:22:04 GMT

Hi Rakesh,

Excelent explain, i does attach .qvw for find duplicates (in all fields of records), this case is typical ¡¡

For NOT omit records duplicates on accounting if exists key duplicates to use RowNo()

good luck, Luis.

Not getting the correct sum figure

Thu, 07 Oct 2010 06:04:59 GMT

Could you pls explain how to include the duplicates in the calculation...

I have the field with 200,200,200,300

here 200 is repeated 3 times...

but wen i do the calculation it will take only one..

how to include all the values...

thanks in advance

Not getting the correct sum figure

ivandrago — Thu, 07 Oct 2010 09:35:33 GMT

Has anyone got any other ideas? I tried SUM(Cost * [Quantity On Hand Usable]) but it is not giving me the result I want?

Not getting the correct sum figure

ivandrago — Thu, 07 Oct 2010 11:16:26 GMT

Now works I have used this formula

sum

(aggr(sum(Cost), [Bin ID],Location,[Location Description],[Part ID],PlaceId))