topic SUM not returning correct result in QlikView

SUM not returning correct result

denwo2010 — Wed, 21 Jul 2010 12:32:39 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 2004.7424 (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

SUM not returning correct result

Wed, 21 Jul 2010 13:25:13 GMT

Expression - Total Mode

mark "Sum of rows"

Anders

SUM not returning correct result

shumailh — Wed, 21 Jul 2010 14:00:37 GMT

Hi,

your sum(Cost) is returning the correct value. There is a problem in your detail sheet, values are repeating because of refrential integrity. Try to check the value of cost by putting it in a list box.

Regards,
Shumail Hussain

SUM not returning correct result

denwo2010 — Wed, 21 Jul 2010 14:33:24 GMT

what do you suggest then? I take it there is no way round this, I had a look at Set Analysis but not sure this will work, this is how the data is I'm afraid as you can see yourself when u put it into a excel spreadsheet the total amount is 2004.7424 and Qlikview seems to only add the distinct values. Help 🙂

SUM not returning correct result

jonathandienst — Wed, 21 Jul 2010 16:49:16 GMT

The chart is only making use of the Parts table (at least for the dimension and the cost expression), and the total is indeed correct if you look at that table alone. You need to chnage the expressions and/or structure to get the result you want, but its getting late here now and I have to go, so I don't have a solution at hand for you.

Maybe someone else can suggest the way out.

Jonathan

SUM not returning correct result

shumailh — Wed, 21 Jul 2010 17:00:19 GMT

The easiest way to resolve this issue is to de-normalize the data. sorry right now i dont know the way to resolve it at the current form of the data.

Regards,
Shumail Hussain

SUM not returning correct result

llauses243 — Fri, 23 Jul 2010 08:44:18 GMT

Append in script ...

Mix:

Load

[Part PK],

//non unique

[Bin ID] //unique

Resident Stock;

Inner

Join Load [Part PK],

//unique

Cost

Resident Parts;

SUM not returning correct result

Anonymous — Fri, 23 Jul 2010 09:16:12 GMT

One approach is to aggregate the values over the unique identifier in the other table, [Bin ID], and change the expression to:

sum(aggr(sum(Cost), [Bin ID]))

This should give you the requested value

SUM not returning correct result

denwo2010 — Fri, 23 Jul 2010 09:43:26 GMT

Johannes, Your formula is returning nothing back?

LLauses, not sure what you mean

SUM not returning correct result

Anonymous — Fri, 23 Jul 2010 09:46:52 GMT

Denwo,

Seems to work fine here. attached. see COP2