Conditional Set Analysis

[#1]

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!

[sunny_talwar]

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

[#3]

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.

[Clever_Anjos · Accepted solution]

PFA a possible solution

[#5]

Anjos,

Just, simply clever!

Thanks,

Tamas

[kwdaniels]

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