topic Re: how to sum calculated absolute values in QlikView

how to sum calculated absolute values

torpe999 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I am trying to sum calculated absolute values but not successfully. I need to calculate total demand accuracy which is

= ( 1- sum (calculated absolute gap deviation)/ sum(planned volume)

but when I try to sum calculated absolute gap deviation, i get sum of gap deviation but not sum of absolute gap deviation though in the table total field it's shown correctly. What should i do in order to sum absolute values and get correct total demand accuracy?

Produkto Nr.	Parduota (sold)litru	Suplanuota (planned)litru	abs gap dev	gap dev
	40 320	25300	15740	15020
S00132	8 640	9000	360	-360
S05301	5 760	4300	1460	1460
S05303	25 920	12000	13920	13920

Total demand accuracy

0,406324111

and it should be: should be 1-(15740/25300)=0,377

Re: how to sum calculated absolute values

chrismarlow — Mon, 10 Jun 2019 19:48:33 GMT

Hi,

You need to use AGGR wrapped around the bit you taking the absolute value to make it apply to each row, even in the total, so I think something like this will work, but will depend on how your totals have come together;

1-(sum(aggr(fabs(sum([Parduota (sold)litru])-sum([Suplanuota (planned)litru])),[Produkto Nr.]))/sum([Suplanuota (planned)litru]))

Cheers,

Chris.

Re: how to sum calculated absolute values

torpe999 — Tue, 11 Jun 2019 05:56:07 GMT

great, thanks! It worked!