topic Set Analysis and Excluded Field Values in QlikView

Set Analysis and Excluded Field Values

s2ncpgwx — Fri, 22 May 2015 06:59:00 GMT

The attached simple example shows my attempt at using set analysis to show results for a specific field and value (value (Overall) for field [Attribute Name]). My set analysis expression is:

=sum({< [Attribute Name]={"?Overall?"} >} [Sync Count])

The desired result should look like the (Overall) chart visible in the Trellis when all selections are cleared. The set analysis does limit the results and the results are always correct. However the side-effects of selections in the [Attribute Name] listbox still cause problems. If any [Attribute Name] value other than (Overall) is selected, some of the [Sync Status] dimension values become excluded. The colouring and ordering of the dimension is disrupted.

Can anyone help with a set analysis expression that handles side effect exclusions on Sync Status, while allowing normal filtering based on selections in other fields?

Re: Set Analysis and Excluded Field Values

tresB — Fri, 22 May 2015 07:14:47 GMT

To keep color intact you could re-write expression for background color like:

=rgb(only({1}[Status Red]), only({1}[Status Green]), only({1}[Status Blue]))

[Sync Status] is associated to [Attribute Name] field, hence the side effect. To keep it unaffected, you could think of alternate state or creating a field in the script and keep it unassociated.

Re: Set Analysis and Excluded Field Values

s2ncpgwx — Sun, 24 May 2015 22:59:57 GMT

Thanks tresesco. Your background colour expression works perfectly and applying the same approach to the sort expression fixed that too. It took a little while to understand why this worked -- until I remembered that set analysis disregards selections but not dimensions.