topic Set Operator on Aggr function? in QlikView

Set Operator on Aggr function?

danielact — Thu, 24 May 2012 14:28:51 GMT

I'm currently using the following dimension:

pick(match(num(aggr(rank(aggr(sum(Number),Type,Year),4,1),Type,Year)),

1000,500,200,100,40,20,10,1),'1/10','1/20','1/50','1/100','1/250','1/500','1/1000','1/10000',)

I'd like to use a Set Operator to ignore any selections - that would be {1}, right?

At first I thought I'd only need it on the Sum part, but the value is changing when I make a selection.

Is there a way to get this to ignore any selections?

Set Operator on Aggr function?

CELAMBARASAN — Thu, 24 May 2012 14:40:49 GMT

Hi,

Are you using type and year as dimension?

Can you explian what you trying to do..

Celambarasan

Set Operator on Aggr function?

danielact — Thu, 24 May 2012 14:43:56 GMT

I'm using Type as a dimension.

I want to sum "Number" by year, and then rank them. I then pull out certain rankings, and use them as a dimension.

My expression is just sum(Number) - and I did remember to apply the {1} to the expression also when trying to work that out.

I did just realize that I have an extra aggr statement in my current formula which I don't really need, but that won't fix the issue.

Re: Set Operator on Aggr function?

jonathandienst — Thu, 24 May 2012 15:03:56 GMT

I think you need the {1} on the Rank and the Sum.

Regards

Jonathan

Re: Set Operator on Aggr function?

danielact — Thu, 24 May 2012 15:15:49 GMT

It won't allow it, everything goes to null then.

Re: Set Operator on Aggr function?

danielact — Thu, 24 May 2012 17:13:59 GMT

I got it to work somwhat, but I'm still stuck a bit. Here's the issue: when I have my filters set so that every year is still possible, it works. However, when my selections filter out a year, the function doesn't work. I believe this is because the aggr statement can't apply the sum across a year that it doesn't find - although for the years it does have, it will pull the full sum, not only what it's filtered down to.

How can I get the aggr statement to work across all years?

Oh, here's my Dimension code now:

pick(match(num(aggr(rank(sum({1}Number),4,1),Type,Year)),

1000,500,200,100,40,20,10,1),'1/10','1/20','1/50','1/100','1/250','1/500','1/1000','1/10000',)

Set Operator on Aggr function?

CELAMBARASAN — Thu, 24 May 2012 17:24:00 GMT

Hi,

Can you post a sample data?

Instead of aggr change Sum(TOTAL<Type,Year> Number) and try it.Aggr doesn't ve set analysis to restrict the selections.

Re: Set Operator on Aggr function?

danielact — Thu, 24 May 2012 17:34:49 GMT

The data set is pretty large...

I have another field I'm not showing in this chart, called Department. Let's say I have 2 departments - A and B.

Let's say Dept. A has Year valued 1 - 5000 and B has 5001-10000.

I want to be able to aggr over all 10000 years, even if I've filtered to just one department.

The sum(Total) isn't working - and it's for a dimension, so it needs an aggr function. Total doesn't ignore selections, so that won't work.