Delete 'row' if it does not fulfill condition *URGENT

[n1ef5ng1]

Hi all this is my code

(

sum({<Service_Type_Indicator = {'MED'},OPS_Direction = {'T'}>}board_PPL) + (sum({<Service_Type_Indicator={'MED'},OPS_Direction = {'T'}>}alight_PPL) * 2))
/
(Sum(aggr(if((Service_Type_Indicator='MED') and OPS_Direction = 'T' ,
(Capacity)),Service_Type_Indicator,Month_Year,OPS_Direction,Bus_Name)) * 2)
* 100

This code basically capture how well the bus is utilize. It check on how many passenger is loaded and how many passengers are alighted in the journey. on the sum (aggr), it check the bus capacity.

Say if there are average 40 passengers on the bus and the bus can contain 50 pax. it has a 40/50 * 100 = 80% utilization rate.

This code will do just that.

However, I would want to eliminate bus that has <20 people being boarded and alighted the bus.

I have a field call Activity_Code that contain LOAD and DISCHARGE. a passenge who board on the bus are group under LOAD and a passenger who alight the bus in the middle of the journey are call DISCHARGE.

I want a condition where this formula applies to BUSES that has LOAD and DISCHARGE that has always > 20.

Imagine I have two record of bus

Bus A has 18 Passenger alighted and 400 passengers boarded.

Bus B has 21 passenger alighted and 21 passengers boarded.

I would want to remove bus A record completely from this formula even though it carries  more passengers as it does not fullfill the above rule mentioned.

How can I do this? Appreciate alot if anyone can solve this for me.

I tried using if((Activity_Code = 'LOAD' and Load_PPL >=20) and (Activity_Code = 'DISC' and DISC_PPL >= 20,

(

sum({<Service_Type_Indicator = {'MED'},OPS_Direction = {'T'}>}board_PPL) + (sum({<Service_Type_Indicator={'MED'},OPS_Direction = {'T'}>}alight_PPL) * 2))
/
(Sum(aggr(if((Service_Type_Indicator='MED') and OPS_Direction = 'T' ,
(Capacity)),Service_Type_Indicator,Month_Year,OPS_Direction,Bus_Name)) * 2)
* 100 )

but it does not help.

(not a syntax problem and I am unable to give sample file for this.) thanks

[swuehl]

Maybe like

if( sum( {<Activity_Code = {'LOAD'} >} Load_PPL) >=20  and sum( {< Activity_Code = {'DISC'} >} DISC_PPL) >= 20,

sum({<Service_Type_Indicator = {'MED'},OPS_Direction = {'T'}>}board_PPL) + (sum({<Service_Type_Indicator={'MED'},OPS_Direction = {'T'}>}alight_PPL) * 2))
/
(Sum(aggr(if((Service_Type_Indicator='MED') and OPS_Direction = 'T' ,
(Capacity)),Service_Type_Indicator,Month_Year,OPS_Direction,Bus_Name)) * 2)
* 100 )

)

But it's quit hard to answer your question without knowing your data and model.

[#3]

Hi,

Try this

Create a variable like

vCountLoad=Count( {<Activity_Code = {'LOAD'} >} Load_PPL)

vDischargeLoad=Count( {<Activity_Code = {'DISC'} >} DISC_PPL)

In expression

IF(vCountLoad>20 AND vDischargeLoad>20,

sum({<Service_Type_Indicator = {'MED'},OPS_Direction = {'T'}>}board_PPL) + (sum({<Service_Type_Indicator={'MED'},OPS_Direction= {'T'}>}alight_PPL) * 2))
/
(Sum(aggr(if((Service_Type_Indicator='MED') and OPS_Direction = 'T' ,
(Capacity)),Service_Type_Indicator,Month_Year,OPS_Direction,Bus_Name)) * 2)
* 100 )

,''

)

)

[n1ef5ng1]

Are we able to create a if statement on a sum in expression. Sometime it

work but some it dun. For this expression I created a basic if statement on

the expression but it gives me a - output

On 2 Sep, 2013 6:58 PM, "Amuthabharathi Subramanian" <