Set Analysis for Filtering Column

[#1]

Hi All,

Question, I am using this expression:

Count(distinct {$<Year={$(==max( {1} Year ) -1 )},LineSaleAmount={">0"}>} GroupName)

to filter negative, 0 and null columns from my count column on a table

It works fine except it only filters individual rows, i'd prefer to filter by the total, by GroupName

This does not work but it gives the general idea of what i'm trying to do.

count({$<Year={$(==max( {1} Year -1))}>},aggr(Sum(LineSaleAmount), GroupName)={">0"} GroupName)

This sorta works but only when a specific dimension value is selected

sum(if(aggr(Sum({$<Year={$(==max( {1} Year -3 ))}>} LineSaleAmount), GroupName) > 0,1,0))

I'd appreciate any help, Thanks

Tim

[johnw]

Something like this, I think? Assuming I got all the syntax right, the idea is that we "select" only group names that had a positive total line sale amount in the most recent year. Then we count them. The "*=" says to not count a group name if you've excluded it with selections. Not sure if that's what you want, or if you'd want a straight "=".

count({<GroupName*={"=sum({<Year={$(=max({1}Year)-1)} LineSaleAmount)>0"}>} distinct GroupName)

Set analysis like that is probably fastest, but I'd expect something like the below to also work, which I think is the approach you were trying to take in the last expression you wrote. I think this will be equivalent to the "*=" approach.

sum(aggr(if(sum({<Year={$(=max({1}Year)-1)} LineSaleAmount)>0,1),GroupName))

Again, I might have syntax or other errors.

[#3]

Thanks John,

I'm still struggling with the syntax for the set analysis solution.

I wish there was a wizard available for building/validating set analysis expressions.

Creating the correct syntax is way harder than it needs to be.

Tim