SUM not returning correct result

[denwo2010]

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

[#2]

Hi

Expression - Total Mode

mark "Sum of rows"

Anders

[shumailh]

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

[denwo2010]

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 🙂

[jonathandienst]

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

[shumailh]

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

[llauses243]

Append in script ...

Mix:

Load

[Part PK],

//non unique

[Bin ID] //unique

Resident Stock;

Inner

Join Load [Part PK],

//unique

Cost

Resident Parts;

[Anonymous]

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

[denwo2010]

Johannes, Your formula is returning nothing back?

LLauses, not sure what you mean

[Anonymous · Accepted solution]

Denwo,

Seems to work fine here. attached. see COP2