topic Re: Set Analysis and excluding selection in QlikView

Set Analysis and excluding selection

picturebox — Thu, 17 Nov 2011 17:15:45 GMT

Hello all,

I am using the expression (here simplified)

sum( { $ <WeekIndex = {1}, CW= , StoreNumber=>} UnitMovement)

this works as designed i.e. it gets me the sum of UnitMovement for Weekindex = 1 out of 106 Weeks, and it ignores if a user has selected anything on the fields CW or StoreNumber . But if the user has selected anything else over another field that will not be ignored with the above statement. How can I get QV to ignore ALL selection other than the WeekIndex=1 (this may sometimes also be a range 1-10 or 12-50 or whatever. Meaning I need to flexible to give the WeekIndex, but ignore all other selections.

If I use {1} I get the total set incl. WeekIndex from 1 to 106, which is not what I want either.

Anyone know how to do this?

By the way, this is still QV9 SR7 but should be the same for 11 I think.

Regards,

Petra

Set Analysis and excluding selection

RedSky001 — Thu, 17 Nov 2011 17:36:55 GMT

I don't see why the following wouldn't work it removes the current selection then you select weekened back to 1, do you have an example file, might help.

sum( { 1 <WeekIndex = {1}, CW= , StoreNumber=>} UnitMovement)

Re: Set Analysis and excluding selection

johnw — Thu, 17 Nov 2011 21:49:00 GMT

This might be good enough. However, other selections DO affect what is possible in WeekIndex. So it ignores them directly, but includes them indirectly.

sum({1<WeekIndex=P()>} UnitMovement)

Here's a better solution if that's not good enough. It properly ignores everything, even their indirect effects. It ONLY cares about selections in WeekIndex.

sum({<$(=concat({<$Field-={'WeekIndex'}>} '[' & $Field & ']=',','))>} UnitMovement)

Re: Set Analysis and excluding selection

picturebox — Fri, 18 Nov 2011 14:47:08 GMT

Hello,

thank you both for the input. Always thought (did not try it out!!!) that the 1 will always take total set without regard ... for my purpose the simpler first solution will work fine since the user will not be accessing the field WeekIndex. The more compley one I will be using to be able to include some and exclude some selections.

Thanks for making me just a little bit wiser once again.

Petra

Re: Set Analysis and excluding selection

picturebox — Fri, 18 Nov 2011 14:50:55 GMT

John,

I wonder how the WeekIndex can be included indirectly. Can you elaborate perhaps?

Thanks,

Petra

Re: Set Analysis and excluding selection

johnw — Fri, 18 Nov 2011 17:12:35 GMT

PictureBox wrote:
John,
I wonder how the WeekIndex can be included indirectly. Can you elaborate perhaps?
Thanks,
Petra

In the attached example, we're trying to ONLY pay attention to Customer selections, while ignoring selections in all other fields. The four straight tables show four different solutions. The second straight table shows the {1<Customer=P()>} approach.

Select Apple for Product. Note that the count in the second chart has gone down from 6 to 5. This happens because although we've selected nothing in the Customer field, only Ann and Bob purchased Apples, so only those two customers are possible. P() is restricting us to possible customers, even if we haven't made any customer selections. So although we're ignoring the Product selection directly (or the count would be 2), we ARE paying attention to it indirectly, in the sense of how it constrains our possible customer values.

That may well be a very useful feature in many cases, just not in this case.

Re: Set Analysis and excluding selection

johnw — Fri, 18 Nov 2011 17:16:24 GMT

PictureBox wrote:
...Always thought (did not try it out!!!) that the 1 will always take total set without regard ...

1 means you're STARTING with the total set of all data, ignoring all selections. However, you can then modify that set as desired by adding additional restrictions in the expression.