topic Re: =if(....,sum) on expression possible? in QlikView

=if(....,sum) on expression possible?

n1ef5ng1 — Wed, 11 Sep 2013 02:08:34 GMT

Hi gurus,

I have this table with Month_Year, Plane_Name, Service_Type_Indicator, Discharge_Cargo, Load_Cargo, Total_Cargo, TXIT_Cargo and Capacity.

For this report, I would want to check how utilize the plane is by service type indicator.

Service Type indicator is the route the plane takes

Total_Cargo is Discharge Cargo + Load Cargo

TXIT_Cargo is how many cargo it stays on the plane without discharging and loading

Capacity is how many cargo the plane is able to handle.

Another table consist of Activity_Code where it will check Load or Discharge Cargo.

And the Total_Cargo where it shows the cargo it discharge by using the activity_code

To check how utilize the plane is, the formula is.

sum({<Service_Type_Indicator = {'EFAR'}>}Total_Cargo) + (sum({<Service_Type_Indicator={'EFAR'}>}TXIT_Cargo) * 2))
/
(Sum(aggr(if(Service_Type_Indicator='EFAR',
(Capacity)),Service_Type_Indicator,Month_Year,Plane_Name)) * 2)
* 100

In short , (TOTAL CARGO + (TOTAL TXIT CARGO * 2))/ Capacity * 2

Please look at the sample file for better clarity.

Now my problem is, of the 8 plane name as shown on the sample file. I would want to remove plane that has Discharge Cargo OR Load Cargo that is lower than 50.

Meaning plane name Boeing, Zoom and Vean should not be included in the expression as it will disrupt the %. (Business context).

if(Activity_Code = 'LOAD' and TOTAL_cargo > 50) (This logic)

Is there anyway I can do this to add on to my expression code? My expression right now applies the formula but it includes the eight planes..

Thanks very much for spending the time to read and understand. appreciate it.

Re: =if(....,sum) on expression possible?

Wed, 11 Sep 2013 02:37:41 GMT

Please see attached, here's how I modified the set:

=(sum({<Service_Type_Indicator = {'EFAR'}>}if( Total_Cargo > 50, Total_Cargo)) + (sum({<Service_Type_Indicator={'EFAR'}>}if( Total_Cargo > 50, TXIT_Cargo)) * 2))
/
(Sum(aggr(if(Service_Type_Indicator='EFAR' AND Total_Cargo > 50,
(Capacity)),Service_Type_Indicator,Month_Year,Plane_Name)) * 2)
* 100

I only saw discard or load cargo, so went with one condition filtering for Total_Cargo > 50.

Hope that helps,

Matt

Re: =if(....,sum) on expression possible?

n1ef5ng1 — Wed, 11 Sep 2013 02:44:30 GMT

thanks. does this comes with attached file so that I can see the expression

Re: Re: =if(....,sum) on expression possible?

Wed, 11 Sep 2013 02:46:31 GMT

Yes, I attached a version with the updated formula. Perhaps I didn't save it, have attached again.

Re: =if(....,sum) on expression possible?

n1ef5ng1 — Wed, 11 Sep 2013 03:33:30 GMT

hmm but the value is the same as my original table with all the planes come into play. i m thinking of (if(activity_code = load and total_cargo> 50) and activity_code = disc and total_cargo> 50

Re: =if(....,sum) on expression possible?

michael_anthony — Wed, 11 Sep 2013 04:47:54 GMT

Could try.

Sum(Aggr(If(Min( If( Activity_Code = 'Load' OR Activity_Code = 'Disc' ,Total_Cargo)) < 50,Null(),
(sum({<Service_Type_Indicator = {'EFAR'}>} Total_Cargo)
+
sum({<Service_Type_Indicator={'EFAR'}>} TXIT_Cargo) * 2 ))
,Plane_Name))

/

Sum(Aggr(If(Min( If( Activity_Code = 'Load' OR Activity_Code = 'Disc' ,Total_Cargo)) < 50,Null(),
(Sum(If(Service_Type_Indicator='EFAR',Capacity)) * 2)
) ,Plane_Name))
* 100

This will exclude the planes with load Total_Cargo on the Load/Disc level from the calculation. It does imply that the total value works out the total Cargo / total Capacity rather than taking an average across the planes.

This formula may not work properly if you introduce more months and may need modify it to include Month_Year in the Aggr and then possibly a TOTAL <Plane_Name> within the Min functions to work properly.

Re: =if(....,sum) on expression possible?

n1ef5ng1 — Wed, 11 Sep 2013 06:00:09 GMT

it prompt me an expression error. do u have the sample file?

Re: Re: =if(....,sum) on expression possible?

michael_anthony — Wed, 11 Sep 2013 06:10:59 GMT

Attachment attached.