Set analysis: ColA <= ColB

[#1]

I want to include everything in my set where Column_A has a value between 5 and 12, and where the value in Column_A is smaller or equal to the value in Column_B. In pseudo code it would look something like this:

(Column_A >= 5 AND Column_A <=12)

AND

(Column_A <= Column_B)

How can I achieve this in set analysis? I have tried the following:

{1<Column_A = {">=5<=12"}, Column_A = {"<=$(=[Column_B])"}>} [MyMeasure]

This does not work. I do not know how to select the (Column_A <= Column_B) part.

Is this possible using set analysis?

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

The expression should look like the following

Sum({< Column_A = {">=5<=12"}, Column_A = {"=Column_A <= Column_B"} >} [MyMeasure])

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Try This:

Sum({<ColumnA = {'>=5<=12'}>}if(ColumnA<=ColumnB,Value))

[#4]

Thanks Miguel, that works excellent!

[#5]

From the example Miguel gave me I created the following simplified comparisson expression:

SUM
(
   {
      <[ColA] = {"=[ColA] <= [ColB]"}>
   }
   [Counter]
)

This really does an excellent job, but fails in a specific case. It took me a while to narrow it down, but the following example explains my problem.

I have created a simple inline table

MyTest:
LOAD * INLINE [
    Key, ColA, ColB, Counter
    1, AA, BB, 1
    2, CC, DD, 1
    3, EE, EE, 1
    4, FF, GG, 1
    5, EE, BB, 1
];

From this table I created the following table

Dimensions are Key, ColA and ColB.

All and Compare are both expressions. Where All contains

SUM(Counter)

and Compare contains the set analysis expression comparisson mentioned

SUM
(
   {
      <[ColA] = {"=[ColA] <= [ColB]"}>
   }
   [Counter]
)

In the column Compare should appear a 1 whenever ColA is smaller or equal to ColB. This is the case for the rows with Key 1, 2, 3 and 4.

As you can see in the table displayed above the row with Key 3 does not display a 1 in the Compare column. But here comes the strange part. When I select the row with Key 3 it does display a 1:

This seems to happen because the value EE appears twice in ColA:

But when I isolate the value, and make sure that it does not occur more than once in the selection, it no longer shows the wrong value:

Is there a way I can make the Compare expression always show the correct value?

[Miguel_Angel_Baeyens]

Hello,

In that case (row to row comparison, set analysis is used once and for the whole chart), I'd use a conditional instead of the set analysis

Sum(If(ColA <= ColB, Counter))

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#7]

Although the conditional fixes the problem, it seems slower than set analysis. I have a lot of expressions and when I use set analysis it is evaluated really fast. But when I use the conditionals it is pretty slow.

It really surprised me that QlikView shows the behaviour I explained. And for me it is not logical.

I would really like to understand why this happens. This way I might be able to find a way to work around it. Can you explain why QlikView behaves this way?

[Miguel_Angel_Baeyens]

Hi,

Set analysis is calculated once for the whole chart, and not value by value. That means that in

Sum({< ColA = {"=ColA <= ColB"} >} Counter)

ColA and ColB are evaluated for the whole chart, and thus is returned that value "EE" (one value) is not lower than or equal to "EE" and "BB" (two values). My guess is what is happening on the background is that QlikView asks "which value should be compared to which?" because there is no 1:1 relation, and returns false. If you use here any aggregation function such as MaxString or Min this will not work as well, because it will get the MaxString or the Min for the whole chart, and not for the current line.

The conditional, although performing poorer, does match "line by line", or value by value, and thus returns the correct results.

Hops that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#9]

Ok, so we are assuming that QlikView automatically returns false, because it does not know which of the two values to compare it to?

I have tried a workaround that would eliminate the doubles:

First I added and extra column to my inline table (Identifier)

MyTest:
LOAD * INLINE [
    Key, ColA, ColB, Counter, Identifier
    1, AA, BB, 1, 1AA
    2, CC, DD, 1, 2CC
    3, EE, EE, 1, 3EE
    4, FF, GG, 1, 4FF
    5, EE, BB, 1, 5EE
];

The Identifier column is a concatenation of Key and ColA.

Then I altered the set analysis expression as follows:

Sum({< Identifier = {"=Identifier <= $(=Key & ColB)"} >} Counter)

I really hoped that this would fool QlikView into making the right match. But it has no success. What do you think?

[Miguel_Angel_Baeyens]

Hi,

Again, you are asking to evaluate a Key and a ColB for which several values exist (keys 3 and 5 and ColB "EE" and "BB").

$(=Key & ColB)

Is not evaluable, so the issue persists. For what it's worth, try the sames expression in a text object:

=Key & ColB

Select "EE" in ColA. It will display "-" (null) because there are two possible different values for one of the two fields in the expression. Now select "AA" in ColA, "1BB" will be displayed.

Said in different words, with 5 rows of your data (so two "EE" values in ColA) using

Sum(If(ColA PRECEDES ColB OR ColA = ColB, Counter))

Will return different than

SUM({< [ColA] = {"=[ColA] PRECEDES [ColB]", "=[ColA] = [ColB]"}>} [Counter])

In the first expression, it will return 1 for "EE", "EE" and zero for "EE", "BB", whilst the second will return zero for both, since it's unable to match row by row in the chart (where same dimension -ColB- have different values EE and BB, although different values in other field like Identifier in the data model, but not used in the chart).

Now take the line 5 out o the script and reload. Both expressions will return 1 in the same chart, since value to value comparison is possible.

Or even add a new line with both ColA and ColB with the same value "EE". Again, it will work for both conditional and set analysis.

Going back to the text object mentioned above, try now that ColA and ColB have "EE" as values clicking on "EE" in ColA. You will indeed get "EE" (without identifier) instead of the "-" when both are different.

In this case, since your analysis requires a line by line (value by value) comparion in the chart, set analysis is not the solution.

Hope this explains how set analysis is used by QlikView when we say it's calculated "once for the whole chart".

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica