Aggr Complex! with attached sample file

[n1ef5ng1]

Hi guys,

Sample file as attached. I have three fields, namely Cargo_Ops, Plane_Ops and TEU. APC is the company that ship cargo via air flight, the company do owns their own plane too for their own transportation. Sometimes APC will use other company aircraft to ship their cargo for business context.

We will like to check how monopolize APC is, meaning whether do they depend on other lagely or by themself. Therfore we have created a formula as shown here.

Cargo_Ops	Plane_Ops	TEU	TEU/Total(TEU)	Square
APC	APC	34085	0.604590525	0.36553
APC	DOL	3017	0.053514731	0.002864
APC	HNM	1399	0.024815084	0.000616
APC	OCB	8210	0.145626763	0.021207
APC	ABF	8180	0.145094631	0.021052
APC	SER	789	0.013995069	0.000196
APC	RAK	596	0.010571687	0.000112
APC	BTA	55	0.000975575	9.52E-07
APC	ELS	46	0.000815936	6.66E-07
		56377		0.411578

Here is the table as per test.qvw. Total TEU for APC cargo is 56377. Out of 56377, 34085 cargo use APC Plane to ship APC cargo.

3017 cargo use DOL Plane to ship APC cargo. In fourth column, we use 34085/56377 to derive 0.604. Basically is Column(3)/sum(TOTAL(Column(3) in Qlikview context even though this expression gives error. After we got the value which shows on the fourth column, we square the value, 0.604 * 0.604 to get the value of the last column which is 0.36553. After which we will then add up all these value to get 0.411578(in bold). This is the value that we are looking for. We are hoping that all these can be done in one expression without any 'workings'. Is this possible in Qlikview? I know aggr is the way out but I have no idea how to do such a complex maths formula.

Thanks alot to those who spend the time reading these, appreciate it.

[Gysbert_Wassenaar]

See attached qvw

[n1ef5ng1]

Thanks! i actually amended the test qvw file. it comes with additional cargo_ops. is it the same method?

[n1ef5ng1]

The only problem i face is

sum

(total <cargo_ops> TEU)

I can't use this TOTAL as my dimension do include Month_Year, with this code it will sum all the teu for this particular cargo_ops. Is there any code that i can replace this to show the total teu for the month

[Gysbert_Wassenaar]

replace cargo_ops with Month_Year if you want to total per monthyear instead of cargo ops.: sum(total <Month_Year> TEU).

[n1ef5ng1]

Thanks alot. to edit is sum(total<Month_Year,cargo_ops>) to get the month total tput for cargo ops. woo!

[n1ef5ng1]

Hi all and specially attention to G Wassenaar,

You have helped me solve the initial problem of this performance indicator, hope you still remember.

Now i have an issue with new attached fiel. There are three cargo ops call APC, CKK and DIK

I would want APC and CKK to group together their value and compute the performance.

Is it possible? For now your code that you gave me are able to generate individual performance indicator of cargo ops. But APC and CKK are in the same company therefore I need to merge their TEU value

[Gysbert_Wassenaar]

Try this expression as calculated dimension in the Individual Cargo Perf straight table: =if(match(Cargo_Ops,'APC','CKK'),'APC & CKK Company',Cargo_Ops)

[n1ef5ng1]

it will calculate individually and sum out the two Cargo-OPs

Say APC has performance of 0.4 and CKK has a perfomance of 0.2, it will show 0.6

That is not the case as I would want them to act as a whole and combine

[n1ef5ng1]

=

sum(aggr(sum(TEU)/sum(total <Month_Year,Cargo_OPS> TEU) * sum(TEU)/sum(total <Month_Year,CO_Operator_Group> TEU) , Cargo_OPS> ,VO_Line_Group_Code,Month_Year))

This is my code in expression