8 Replies Latest reply: Oct 6, 2014 9:16 AM by Ben Roberts

Set Analysis: Show Excluded and Refine Results

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

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

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

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

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

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

• Re: Set Analysis: Show Excluded and Refine Results

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

jontydkpi 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.