Show full list of items when using aggr() in listbox

[timsworldroamer]

Hi everyone,

I'm in a situation where I want to show only items exceeding certain sales figures (adjustable using a slide bar) in a listbox, using an aggregate function:

=if(aggr(sum(sales), item)>$(vSales),item)

The only problem is when I select an item from the list, it displays only the selection(s) rather than the full list of applicable items (just A rather than A, B and C).

Is there a way to get around it?

[swuehl · Accepted solution]

=if(aggr(sum({<item=>}sales), item)>$(vSales),item)

[sunny_talwar]

Adding {1} should also work:

=If(Aggr(Sum({1}sales), item) > $(vSales), item)

[swuehl]

Right, but it will also make the filter itself insensitive to user selections, won't it?

[sunny_talwar]

Yes you are right, but I was not sure if we need user selections in other fields to impact Sum of sales expression or not. May be this Aggr() function is only to do a comparison to vSales variable regardless of selection in any field. I may be wrong, but just listing out another possibility

[timsworldroamer]

Thanks - for some reason it totally didn't occur to me putting item back in the set analysis would do the trick. Thanks so much for the quick response.

[timsworldroamer]

Thanks Sunny for the input. Not exactly a direct solution to my problem but will note your approach as potential candidate to another problem in the future. Appreciate your response here!