Set Analysis - Exclude Values

[ccifaldi]

I am trying to use set analysis to reach a subset of data. The subset of 1 to 2 with banks = 52. The subset of cash position with extremely easy = 27. I need to further dig down and have the subset of extremely easy AND 1 to 2. In this example the data should export 6. Instead of getting 6 Qlik is adding 52 and 27 and giving me the combined total of 79. Any help is appreciated. Thanks. 

[marcus_sommer]

You may try something like this:

count({< Question = {'a', 'b'}, Response = {'x'}> * < Question = {'a', 'b'}, Response = {'y'}>} Response)

- Marcus

[ccifaldi]

Unfortunately this yields a result of 0 rather than 6, but it is closer than 79! Thank you for your help. Do you know of any other operators that may help with this other than E() and *? 

[marcus_sommer · Accepted solution]

It's not a matter of the operators else how the data are stored and how the data-model is designed and in which dimensional context the query should happens - if and how you could apply AND conditions between the values of a single field. The reason is quite often that the field within the queried context has usually only a single value and therefore each AND comparison must fail. But often it could be bypassed with another approach.

So you may try:

count({< Question = {'a', 'b'}, Response = {'x', 'y'}>} Response) *
-(count({< Question = {'a', 'b'}, Response = {'x','y'}> distinct Response)=2)

which tries to outsource the condition within an extra calculation for a boolean check.

Another method would include an aggregation of responses, like:

if(concat({< Question = {'a', 'b'}, Response = {'x', 'y'}>} distinct Response, '|') = 'x|y',
count(count({< Question = {'a', 'b'}>} Response))

which merged the multiple responses into a single string which is then evaluated. Depending on the field-values from Response it could be difficult to determine the sorting and therefore the output-string and then you may need wildmatch() to search for your wanted values. Further this logic might be need to be wrapped with an aggr() to consider the appropriate dimensional context (usually if the calculation needs to be done against different dimensions as a re used within the chart).

Beside this I suggest to consider to replace the string-values with numbers which could simplify such expression and depending on the scenario you may be also able to sum() the responses or finding the min/max value of them if there is any hierarchy included.

- Marcus

[ccifaldi]

For posterity, replacing string-values with numbers did the trick. Thank you!