topic How to get the correct aggregated sum? in QlikView

How to get the correct aggregated sum?

Fri, 19 Nov 2010 18:31:16 GMT

Hi Gurus,

Probably a simple answer to this, but I am a QlikView freshman.

I need to sum all rows (sum of CostCompFrom) to get the right Amount, but I can not sum the Quantity rows (the right value should be 204, not 6121).

1) How can I achieve this and get the result in one row if I remove the CostCompFrom dimension in a chart?

2) How would I achieve the same result by using a load statement?

Thanks in advance,

Freddy

Data (simplified):

Week	Day	OrderNumber	CostCompFrom	Quantity	Amount To
				6 121	7529,38
24	18	WC3000402	1000	204	437,72
24	18	WC3000402	2000	204	142,34
24	18	WC3000402	2010	204	259,77
24	18	WC3000402	3000	204	203,61
24	18	WC3000402	3010	204	146,12
24	18	WC3000402	3012	204	393,34

How to get the correct aggregated sum?

john_duffy — Fri, 19 Nov 2010 20:40:45 GMT

Hello.

I'm not sure if I'm understanding your data structure correctly. You should be able to create a chart with Week, Day, OrderNumber and Quantity as dimensions and an expression Sum([Amount To]).

To achieve this in a load script something like:

Load Week, Day, OrderNumber, Quantity, Sum([Amount To])

From xxxx.qvd

Group by Week, Day, OrderNumber, Quantity

Let me know if this helps.

How to get the correct aggregated sum?

Sat, 20 Nov 2010 15:35:57 GMT

Hi,

Of course! 🙂

This works. Thanks!