Set Analysis: Show Excluded and Refine Results

[Anonymous]

I have been building up a business tool to show our management the number of bankers selling a particular product compared to the bankers who have not sold the product.

For the negative side, I have been able to get the list of bankers not selling the product into a pivot table using the Set Analysis wizard and a bit of luck using the following:

Dimension = Banker

Expression = COUNT({1-$<BANKER/=E(BANKER)>} CLIENT_ID)

The issue I find now, is if I want to refine the results, for example selecting region = EMEA only, the list of 'excluded' then includes the non-EMEA bankers who have sold the product. This makes logical sense as they are no longer part of the 'included' set.

So my question is: Is is possible to refine the results with additional filters, which could include any other field (but I am principally concerned about region), so that the result of this box would be to show all bankers who have not sold product X but are all from region = EMEA.

Thanks,

Ben

[Anonymous]

Let me correct the above and say I was wrong - this did not work at all.

Am I dealing with alternate states here? Any direction would be appreciated!

Thanks,

Ben

[bgerchikov]

Hi Ben,

Try this :

Show distinct count of Bankers that do have sales:

COUNT({$<BANKER={"=Sum({1} Sales ) > 0”}>} DISTINCT BANKER)

Show distinct count of Bankers that do not have sales:

COUNT({$<BANKER={"=Sum({1} Sales ) =0”}>} DISTINCT BANKER)

[Anonymous]

Thanks - the positive works: I can get a list of all Bankers with a result in the product. When I change the >0 to =0, the straight table goes to zero with no contents. I want to get this list showing all that are not part of the result.

I would then like to be able to refine the result further, e.g. by adding region to the restriction while still showing only bankers from that region who have not sold the product.

[bgerchikov]

Ben,

It could be something else in selection that might affect result, like a date. If you can attach sample app, I can take a look.

[jonathandienst]

Hi

Its always harder to search for the absence of something! Search the community for AND NOT - I saw something recently on a similar topic that might help you.

Jonathan

[jonathandienst]

http://community.qlik.com/thread/136321

http://community.qlik.com/thread/62060

[Anonymous]

Many thanks Jonathan. I will take a look through these and see if I can solve this way. Will report back on Monday.

[Anonymous · Accepted solution]

jonathan dienst You set me on the right path. In the end the solution was remarkably simple. I used the results of this community post Re: Show excluded values in pivot table

Essentially you create an if statement on sum of current market value, making >0 = 1 else 1. Then show all values in the dimension tab and suppress zero values on the presentation tab. Simple but elegant.