topic Set analysis behavior in QlikView

Set analysis behavior

Wed, 07 Jul 2010 16:01:45 GMT

Hi everyone, I am building a set analysis expression which I do not want to be affected by selections:

sum({1<AÑO={$(=max(AÑO))}, MES={$(=max(MES))}, BC_TERRITORIO= {"=rank(AGGR(sum({1<LINEA={'LPM'}, AÑO={$(=max(AÑO))}, MES={$(=max(MES))}>} ME_VALUE_INC),BC_TERRITORIO),4,1)<=10"}>} ME_VALUE_INC) / 10

As you can see, I am using prefix 1 to work with the whole set, however it does get affected by selections on any field, for example BC_TERRITORIO, any ideas??, am I misunderstanding the behavior??

Thanks in advance.

Set analysis behavior

Miguel_Angel_Baeyens — Wed, 07 Jul 2010 16:17:02 GMT

Hello Ivan,

If I'm not wrong, you can detach your chart so it will show the same data regardless any selections are done. As far as I understand, that 1 (set identifier) prefixing your set modifier means the set modifier won't use the current selections but all records, while "$" or nothing prefixing the set modifier means the current selections will be used (records returned by current selections).

But it doesn't mean the expression/chart won't be affected by further changes. That's why "Detach" makes sens. Anyone: please, let me know if I'm wrong.

EDIT: Deleted and rewritten

Set analysis behavior

mike_garcia — Wed, 07 Jul 2010 16:25:51 GMT

Hi,

What I see is that there is a function whithin the Set Expression that is using the Selections and therfore everytime you change your selections the Set will change. This function is Rank.

Whenever I use set analysis whithin an aggr expression, I usually need to specify the same set expression for the function outside the aggr expression, in this case Rank. However, Rank does not take Set Expressions. So I would recommend to append the max function before the rank function like this:

sum({1<AÑO={$(=max(AÑO))}, MES={$(=max(MES))}, BC_TERRITORIO= {"=rank(Max({1<LINEA={'LPM'}, AÑO={$(=max(AÑO))}, MES={$(=max(MES))}>} AGGR(sum({1<LINEA={'LPM'}, AÑO={$(=max(AÑO))}, MES={$(=max(MES))}>} ME_VALUE_INC),BC_TERRITORIO)),4,1)<=10"}>} ME_VALUE_INC) / 10

I really have not tested if, once the set expression is outside the aggr, it needs to be in the function inside aggr, in this case sum.

Let me know if it works for you.

Mike.

Set analysis behavior

Wed, 07 Jul 2010 17:47:20 GMT

Thank you for your answer Miguel, however I think I over simplified the requirement, it is not completely static, it should get affected by some selections and if I detached the table, it gets stuck into the same value no matter what.

Thanks again, good thinking

Set analysis behavior

Wed, 07 Jul 2010 17:48:40 GMT

Thanks Mike, it worked just fine for me.

Regards