Set Analysis

[Anonymous]

Hi

I have fields 'Supplier Type' , 'Commission' and 'Return'

To calculate profit for supplier types A and B I just need 'Commission' but for suppler type C I need Commission and Return

The set analysis I have used is

Sum(Commission) + Sum ( {< [Supplier Type] = {"C"}>} Return)

This works great when looking at all the suppliers but if I select type A or B it leaves the Return for type C in the calculation. What am I missing in my set analysis?

Please help

Thanks

[alexandros17]

Try this:

Sum( {< [Supplier Type] = {'A', 'B', 'C'}>} Commission) + Sum ( {< [Supplier Type] = {'C'}>} Return)

let me know

[Anonymous]

Isn't the first part the same as

Sum ( {<[Supplier Type]=>} Commission)

This means I won't be able to select on supplier type A and see the profit on only that as its ignoring the selection.

I want to be able to select on type A and see only the profit related to that supplier type.  In my calculation I am seeing the commission for type A plus return for type C

[Anonymous]

help!

[Anonymous]

how about something like:

= Sum(Commission) + if( index(getfieldselections([Supplier Type]), 'C')>0, Sum ( {< [Supplier Type] = {"C"}>} Return), 0)

this should work for any selection combinations of Supplier Type.

i know we are using an if condition...but this should give you a working solution for the time being.

[Colin-Albert · Accepted solution]

You could add the Supplier Type field a second time in your load script, and name the second copy of the data as ReturnSupplierType.

Then use the expression

     Sum(Commission) + Sum ( {< ReturnSupplierType = {"C"}>} Return)

This will prevent selections in the Supplier Type field affecting sum(return)

[Anonymous]

Thanks.  It works!!