topic Summation Expression in QlikView

Summation Expression

Fri, 18 Dec 2009 22:29:47 GMT

I am wanting to sum the PUM Miles (in yellow) and the LH and LTL Miles (in green) in a Text Box. The LH and LTL Miles are all valid (total is 1018), but only one PUM is valid (total is 15). The value of Total Miles should be 1033. However, my expression returns the total of 1063 (it includes all 3 PUM Miles (total 45). 1063 is incorrect, 1033 is the correct answer.

I can use some help to direct me towards the valid soliution. The field PickupMoveTravelDistance is the PUM Miles, while the TravelDistance field is the LH and LTL Miles

Thanks

Vic

='Total Transit Miles ' &Num(sum({DISTINCT}[PickupMoveTravelDistance]+TravelDistance),'#,##0')

Pro	Number	Pro Weight	PU Weight	PUM Weight	PUM Miles	LH and LTL Weight	MoveType	LH and LTL Miles
2	9844648	1388	5092	25894	15	30789	LH	950
2	9844648	1388	5092	25894	15	1388	LTL	5
2	9844648	1388	5092	25894	15	12304	LH	63

Summation Expression

dvasseur — Fri, 18 Dec 2009 22:48:31 GMT

If PUM Miles has always the same value the following should work:

='Total Transit Miles ' & Num(avg([PickupMoveTravelDistance])+sum({DISTINCT} TravelDistance),'#,##0')

HTH,

David

Summation Expression

dvasseur — Fri, 18 Dec 2009 22:49:08 GMT

If PUM Miles has always the same value the following should work:

='Total Transit Miles ' & Num(avg([PickupMoveTravelDistance])+sum({DISTINCT} TravelDistance),'#,##0')

HTH,

David

Summation Expression

Sat, 19 Dec 2009 01:10:55 GMT

Thanks David. This works when there is a single pro number. It doesn't when there are multiple records. the example below has 5 records for 2 pro numbers.

Vic

Pro	Number	Pro Weight	PU Weight	PUM Weight	PUM Miles	LH and LTL Weight	MoveType	LH and LTL Miles
2	9137913	22262	30000	30000	10	43262	LTL	5
2	9137913	22262	30000	30000	10	43262	LH	863
2	9332660	2116	49509	49509	13	39429	LH	921
2	9332660	2116	49509	49509	13	18225	LH	63
2	9332660	2116	49509	49509	13	44519	LTL	5

Summation Expression

dvasseur — Mon, 21 Dec 2009 16:10:48 GMT

Maybe this then 🙂

='Total Transit Miles ' & Num(Sum(Aggr(avg([PickupMoveTravelDistance]), [Number]))+sum(TravelDistance),'#,##0')

I don't know if the DISTINCT is required for TravelDistance but add it if needed!