topic Re: Conditional Set Analysis in QlikView

Conditional Set Analysis

Wed, 23 Mar 2016 10:41:47 GMT

Hi All,

I’d like to build an expression that allows the users to flexibly change the number of conditions in the aggregations.

The aim is to count column X if column Y,Z,W…ect has VALID values. (In each row, VALID values are flagged with 1 and with 0 if NOT VALID).

That would be a simple set analysis: count({<ValidY = {1}, ValidZ = {1}, ValidW = {1}>} )

However I’d have buttons/text areas that changes variables. Based on that variable (on/off) the expression should/should not consider Y,Z or W flagging.

In this way, the user could see the result of “data quality” in summary, flexibly throwing in conditions to consider, on the fly.

To be more specific, consider this data set:

So, let’s say for me, valid Z attribute is quite important – Y and W, are not that much. (this could be different for other users).

Therefore, I’d hit the “Consider Z” button (changing flagging variable v_ValidZ to be 1), and the expression would return: "You have 80% good data" (only 1 out of 5 is missing), completely ignoring missing Y and W values – that would drag my results.

Please also note, that the focus of the analysis is value X, which could be missing also. In this case, that row should be ignored as well, so focusing only on attributes (Y,Z,W) is not an option.

We would only use attributes to filter data set.

Thanks in advance!

Re: Conditional Set Analysis

sunny_talwar — Wed, 23 Mar 2016 11:37:07 GMT

May be something like this:

If(vVar = 1, Count({<ValidY = {1}>} X),

If(vVar = 2, Count({<ValidZ = {1}>} X),

If(vVar = 3, Count({<ValidW = {1}>} X)))

Where each of your button sets a different value for vVar

Button for Y will set vVar = 1, Z-> vVar = 2, W-> vVar = 3

Re: Conditional Set Analysis

Wed, 23 Mar 2016 12:15:05 GMT

Thanks SunnyT for the quick reply!

However, I am looking for a more scalable and flexible solution.

The problem here, is that I will not only have 3 but at least 15 attributes.

Additionally, this solution cannot handle multiple selection. I did not use mixed selection as an example indeed, but it can happen. So, maybe you are interested in Y and Z but not W. Too much combination to be handled using this "nested if statement" concept.

Re: Conditional Set Analysis

Clever_Anjos — Wed, 23 Mar 2016 12:40:31 GMT

PFA a possible solution

Re: Conditional Set Analysis

Wed, 23 Mar 2016 14:08:28 GMT

Anjos,

Just, simply clever!

Thanks,

Tamas

Re: Conditional Set Analysis

kwdaniels — Mon, 06 Jul 2020 15:44:15 GMT

Hello Clever_Anjos,

Thanks for your solution to this question. Unfortunately I'm a Qlik Sense user so am not able to see what's in the qvw file. Would you be able to post in clear text the set analysis formula you used?

Thanks,

Ken