Qlik Community

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')

ProNumberPro WeightPU WeightPUM WeightPUM MilesLH and LTL WeightMoveTypeLH and LTL Miles
2984464813885092258941530789LH950
298446481388509225894151388LTL5
2984464813885092258941512304LH63


Tags (1)
4 Replies
Highlighted
Partner
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
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

ProNumberPro WeightPU WeightPUM WeightPUM MilesLH and LTL WeightMoveTypeLH and LTL Miles
291379132226230000300001043262LTL5
291379132226230000300001043262LH863
29332660211649509495091339429LH921
29332660211649509495091318225LH63
29332660211649509495091344519LTL5


Highlighted
Partner
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!