Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
danielact
Partner - Creator III
Partner - Creator III

Set Operator on Aggr function?

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?

7 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Are you using type and year as dimension?

     Can you explian what you trying to do..

    

Celambarasan

danielact
Partner - Creator III
Partner - Creator III
Author

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.

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

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

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
danielact
Partner - Creator III
Partner - Creator III
Author

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

danielact
Partner - Creator III
Partner - Creator III
Author

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',)

CELAMBARASAN
Partner - Champion
Partner - Champion

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.

danielact
Partner - Creator III
Partner - Creator III
Author

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.