2 Replies Latest reply: Oct 1, 2014 11:01 AM by Ben Roberts RSS

    Set Analysis: Select All but Update Based on Selection of Another Field

    Ben Roberts

      I am trying to do an analysis to show the depth of sales based on a particular product across the sales for and across region.

       

      I have used set analysis to create a bar chart that shows number of bankers who have sold a product name compared to total banker population, split by region, using a bar chart with region as dimension and an expression of:

      Expression 1: Count(DISTINCT {$} BANKER) / COUNT (DISTINCT {1} BANKER)

      Expression 2: 1-Expression 1

       

      The result of this is a 100% stacked bar that shows how much of a product has been sold by a banker, when the user searches PRODUCT for any product e.g. ACME Company.

       

      This works fine for analysis across all regions, but when I want to apply an additional filter to narrow down to one or two regions e.g. Select REGION = EMEA, I get a bar with 100% values for the regions I have not selected (I would want these to disappear as I have focused on EMEA. Presumably this is the impact of the {1} in the set analysis. I want to limit the content of the 'ALL' to the regions selected.

       

      I assume there is a modification to the set analysis for current selection based on another variable REGION but I cannot find the correct syntax anywhere.

       

      Thanks,

      Ben