topic Re: Set Analysis: Show Excluded and Refine Results in QlikView

Set Analysis: Show Excluded and Refine Results

Anonymous — Thu, 02 Oct 2014 16:59:08 GMT

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

Re: Set Analysis: Show Excluded and Refine Results

Anonymous — Thu, 02 Oct 2014 19:00:39 GMT

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

Re: Set Analysis: Show Excluded and Refine Results

bgerchikov — Thu, 02 Oct 2014 19:17:51 GMT

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)

Re: Set Analysis: Show Excluded and Refine Results

Anonymous — Fri, 03 Oct 2014 12:02:10 GMT

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.

Re: Set Analysis: Show Excluded and Refine Results

bgerchikov — Fri, 03 Oct 2014 13:35:54 GMT

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.

Re: Set Analysis: Show Excluded and Refine Results

jonathandienst — Fri, 03 Oct 2014 14:06:53 GMT

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

Re: Set Analysis: Show Excluded and Refine Results

jonathandienst — Fri, 03 Oct 2014 14:13:01 GMT

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

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

Re: Set Analysis: Show Excluded and Refine Results

Anonymous — Fri, 03 Oct 2014 17:16:06 GMT

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

Re: Set Analysis: Show Excluded and Refine Results

Anonymous — Mon, 06 Oct 2014 13:16:24 GMT

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.