QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Not applicable

Summation Expression

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

Tags (1)
4 Replies
Highlighted
Partner

Summation Expression

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

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

HTH,

David

Highlighted
Partner

Summation Expression

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

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

HTH,

David

Highlighted
Not applicable

Summation Expression

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

Highlighted
Partner

Summation Expression

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!