topic set analysis selective dimension in QlikView

set analysis selective dimension

Thu, 19 Nov 2009 02:23:40 GMT

On some expressions, i would want to only consider certain dimensions and disregard the rest.

For example i have 5 dimensions (Dim1,Dim2,Dim3,Year,Month). I only want to consider ONLY Year and Month. What i've done is:

sum({$<Dim1=,Dim2=,Dim3=> Sales}

This is doable if there's only these few dimensions. But when you have a lot then it becomes unmanageable. I was thinking there must be a way in set analysis to say return everything, then on the set modifier you only pick the dimensions you want considered. Something like this but obviously does not work:

sum({1<Year=Year,Month=Month>} Sales)

Any ideas?

set analysis selective dimension

Thu, 19 Nov 2009 03:29:39 GMT

Try something like this:


Sum( {-<Year=,Month=>} Sales )

It should select everything that involves year and month.

Found it in the help under Set Analysis and then the Set Operator examples (bottom one).

set analysis selective dimension

Thu, 19 Nov 2009 05:30:00 GMT

Mark,

Thanks but i don't think this works. I tried it as well and it just yielded zero all the time.

Isn't that just saying from current selection, exclude year and month unselected? I just need it to only consider month and year and disregard any other selections made.

set analysis selective dimension

Thu, 19 Nov 2009 06:02:32 GMT

You're right ...

A difficult problem to solve.

I will have to think about this more. Hopefully somebody else can give you a solution soon.

set analysis selective dimension

johnw — Thu, 19 Nov 2009 06:03:15 GMT

I think there's an easy way that I'm not remembering, but here's a hard way that would probably work (untested) (edited to add distinct):

sum({1<Year ={'$(=concat(distinct Year ,chr(39)&','&chr(39)))'}
,Month={'$(=concat(distinct Month,chr(39)&','&chr(39)))'}>} Sales)

set analysis selective dimension

Thu, 19 Nov 2009 06:15:21 GMT

Ah, so you're selecting everything where year is in all possible years and the same for month, without having to name them hardcoded ... nice.

Say Year can be 2007, 2008 and 2009, this translates to

sum({1<Year ={'2007','2008',2009'}, ....

set analysis selective dimension

Fri, 20 Nov 2009 02:28:52 GMT

Thanks John, That seems to do it. Hopefully like you said there's a more simpler way for this.

set analysis selective dimension

Fri, 12 Mar 2010 14:51:21 GMT

Has anyone found an easier way to do this? I'm having a similar problem, only I need to take 15 ~ 20 dimensions into consideration, which makes this solution a lot less practical.

set analysis selective dimension

Tue, 15 Jun 2010 10:16:35 GMT

Yes, after looking arround for this for some time I got it:

=sum(
{1< // Disregards all selections with 1
Year=P(Year), // Using element function P() to select the Possible values for Year (this will work with Null(), 1 and many values selected in the field)
Quarter=P(Quarter), // etc...
Month=P(Month)
>}
Amount // Whatever you wanna measure
)

Hope this will help.

Diego

set analysis selective dimension

johnw — Tue, 15 Jun 2010 19:05:01 GMT

Yep, THAT'S the way to do it. Only works in 9.0 as I recall, but I suspect most people are on 9.0 by now. (We're not. )