sum of a column

[fernfrancis]

Hi team, 

I am doing myltiplication of two fields using the below formula

=num((Count(Aggr(NODISTINCT Count(PNR),PNR))*IF(MATCH(BAGGAGE_TYPE,'No BAG'),0,IF(LEN(BAGGAGE_TYPE)>0,num(LEFT(BAGGAGE_TYPE,2)),0))))

The formula works well for a selected data but not when the filtes are removed.  If the filters are removed the answer is always 0. whereas its supposed to add the total weight and five the details. 

[Or]

Near as I can tell, the If() component of your formula is not contained within an aggregation function. As a result, if there is more than one baggage type in the data, it will return null. Fixing it depends on the logic required, but generally speaking it probably needs to be within some sort of aggregation function (possibly the previous count/aggr)

[vinieme12]

Try below

Count of PNR where Baggage_Type IS NOT  'No Bag'

Count({<BAGGAGE_TYPE-={'No Bag'}>}PNR)

[fernfrancis]

Hi Vineeth,

Sorry for the late reply. I have a columns as follows

1. Baggage Type : ( column has data like "No Baggage", " 20 Kg 1 piece" "30 Kg 2 pieces" etc ... )  I am extracting the first two number if weight exist and which inserts 0 if there is no baggage.  using the fx: =IF(MATCH(BAGGAGE_TYPE,'No BAG'),'0',IF(LEN(BAGGAGE_TYPE)>0,num(LEFT(BAGGAGE_TYPE,2)),'0'))

2. Another column present is  Count of passengers which is calculated using the fx : =Count(Aggr(NODISTINCT Count(RPN),RPN))

Both the above two fields have correct data and its validated. 

3. I created another column to calculate the total weight wherein i am multiplying both the columns mentioned above using the fx: 

=num((Count(Aggr(NODISTINCT Count(PNR),PNR))*(IF(MATCH(BAGGAGE_TYPE,'No BAG'),0,IF(LEN(BAGGAGE_TYPE)>0,num(LEFT(BAGGAGE_TYPE,2)),0)))))

This gives me correct result if i filter based on selected RPN, but i dont filter based on PRN it shows 0 and does not give the totl weight

[vinieme12]

Create an actual "Weight" field during data load 

Load *, 

IF(wildMATCH(BAGGAGE_TYPE,'No BAG'),0,IF(LEN(BAGGAGE_TYPE)>0,num(LEFT(BAGGAGE_TYPE,2)),0)) as Weight 

From xxyourdatasource; 

Then use =sum(Weight) for total weight, simple as that