topic Set Analysis for Filtering Column in QlikView

Set Analysis for Filtering Column

Wed, 26 Jan 2011 22:33:29 GMT

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

Set Analysis for Filtering Column

johnw — Thu, 27 Jan 2011 00:58:13 GMT

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.

Set Analysis for Filtering Column

Thu, 27 Jan 2011 16:21:19 GMT

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