Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi,
Are you using type and year as dimension?
Can you explian what you trying to do..
Celambarasan
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.
Hi
I think you need the {1} on the Rank and the Sum.
Regards
Jonathan
It won't allow it, everything goes to null then.
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',)
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.
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.