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?

[#2]

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!

[Miguel_Angel_Baeyens]

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

[#4]

Thanks for the extra info!

[#5]

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?

[#6]

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. <=)