topic Re: Set Analysis in QlikView

Set Analysis

Anonymous — Fri, 20 Jun 2014 14:57:18 GMT

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

Re: Set Analysis

alexandros17 — Fri, 20 Jun 2014 15:00:16 GMT

Try this:

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

let me know

Re: Set Analysis

Anonymous — Fri, 20 Jun 2014 15:07:52 GMT

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

Re: Set Analysis

Anonymous — Fri, 20 Jun 2014 15:39:23 GMT

help!

Re: Set Analysis

Anonymous — Fri, 20 Jun 2014 15:42:46 GMT

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.

Re: Set Analysis

Colin-Albert — Fri, 20 Jun 2014 15:50:10 GMT

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)

Re: Set Analysis

Anonymous — Fri, 20 Jun 2014 16:05:16 GMT

Thanks. It works!!