Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

ben2roberts
Contributor

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

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

Tags (1)
1 Solution

Accepted Solutions
marcarreras
Valued Contributor

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

Try to modify Expression 1:

Count(DISTINCT {$} BANKER) / COUNT (DISTINCT {1<REGION=$::REGION>} BANKER)

2 Replies
marcarreras
Valued Contributor

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

Try to modify Expression 1:

Count(DISTINCT {$} BANKER) / COUNT (DISTINCT {1<REGION=$::REGION>} BANKER)

Highlighted
ben2roberts
Contributor

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

Excellent - works perfectly. Thanks - this will be a massive help in what I am working on.