topic Re: Set analysis: ColA <= ColB in QlikView

Set analysis: ColA <= ColB

Wed, 01 Jun 2011 08:54:29 GMT

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?

Re: Set analysis: ColA <= ColB

Miguel_Angel_Baeyens — Wed, 01 Jun 2011 09:06:16 GMT

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

Set analysis: ColA <= ColB

Wed, 01 Jun 2011 09:12:32 GMT

Try This:

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

Set analysis: ColA <= ColB

Wed, 01 Jun 2011 10:43:35 GMT

Thanks Miguel, that works excellent!

Re: Set analysis: ColA <= ColB

Fri, 03 Jun 2011 14:17:27 GMT

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?

Re: Set analysis: ColA <= ColB

Miguel_Angel_Baeyens — Fri, 03 Jun 2011 15:17:26 GMT

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

Re: Set analysis: ColA <= ColB

Mon, 06 Jun 2011 06:43:21 GMT

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?

Re: Set analysis: ColA <= ColB

Miguel_Angel_Baeyens — Mon, 06 Jun 2011 08:18:46 GMT

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

Re: Set analysis: ColA <= ColB

Mon, 06 Jun 2011 08:37:08 GMT

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?

Re: Set analysis: ColA <= ColB

Miguel_Angel_Baeyens — Mon, 06 Jun 2011 09:49:18 GMT

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

Re: Set analysis: ColA <= ColB

Mon, 06 Jun 2011 13:37:22 GMT

Thank you for taking the time to write such a detailed answer! You really helped me in understanding the workings of it all.

I still want to use set analysis to solve my problem, because it performs a lot better than the conditional expression.

What I did was create some extra columns that make the columns I have to compare unique over the whole dataset. This way QlikView knows which values to compare.

MyTest:

LOAD

Key & ColA AS IdentifierA,

Key & ColB AS IdentifierB;

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

];

I used the newly created columns in the set expression.

SUM
(
   {
      <IdentifierA = {"=IdentifierA <= IdentifierB"} >
   }
   Counter
)

This works excellen and solves my problem. The big plus is that it is really fast when a selection is changed!

Thanks again Miguel!

Re: Set analysis: ColA <= ColB

Miguel_Angel_Baeyens — Mon, 06 Jun 2011 13:47:31 GMT

Hi,

Glad to help, you're welcome and that you have found your way to change the data model. Just one note, that is that in version 10 exist two relational operators to compare, according to the ASCII table, literals or string values. That is the case of "FOLLOWS" that will return true if the left part has a greater ascii value than the right part:

If('AAA' FOLLOWS 'A', true(), false()) // This will return true

The other one is PRECEDES, that is the opposite case:

If('B' PRECEDES 'C', true(), false()) // This will return false

I find those more useful than ">=", "<=" and so.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Set analysis: ColA <= ColB

Mon, 06 Jun 2011 13:53:25 GMT

Thanks for the extra info!

Re: Re: Set analysis: ColA <= ColB

Wed, 17 Sep 2014 03:36:58 GMT

I am trying to do a similar set analysis, but can't get it to work.

My expression is as follows, and this actually works:

Sum({$<SourceType={'Budget'}, SummaryType=, PeriodStat={'Y'}, Month = {'<= 4'}>} If(AC3='510', BalanceLC)) --> returns the sum of the first 4 months

Now I would like to make it more flexible and replace the static 4 in the month with a Count(Distinct Month). Whatever I try, it simply does not return the values that I need.

Sum({$<SourceType={'Budget'}, SummaryType=, PeriodStat={'Y'}, Month = {'<= Count(Distinct Month)'}>} If(AC3='510', BalanceLC)) --> returns 0

Sum({$<SourceType={'Budget'}, SummaryType=, PeriodStat={'Y'}, Month = {'=Month <= Count(Distinct Month)'}>} If(AC3='510', BalanceLC)) --> returns the first months value only

I also tried to use a variable with the expression:

x = Count(Distinct Month)

and then use this variable.

Sum({$<SourceType={'Budget'}, SummaryType=, PeriodStat={'Y'}, Month = {'<= $(x)'}>} If(AC3='510', BalanceLC)) --> returns returns the first months value only

How can I replace the hardcoded 4?

Re: Re: Re: Set analysis: ColA <= ColB

Wed, 17 Sep 2014 04:27:21 GMT

I guess I just found the solution:

=Sum({$<SourceType={'Budget'}, SummaryType=, PeriodStat={'Y'}, Month={'=Count(Distinct Month)'}>} If(AC3='510', BalanceLC))

For some reason, if I use a = instead of <= then it does calculate the sum from 1 - x (i.e. <=)