Creating dimension for bucketing records w/ special constraint on one bucket

[#1]

I have a dataset with 3 columns: Client, Status, Revenue.  Status can be 'Covered' or 'Uncovered'.  Client to Status is a one-to-many relationship (i.e., one row in the data may be Client A // Covered // $100 ... and another may be Client A // Not Covered // $300)

I create a bar chart with a dimension that buckets the Clients based on total Revenue, regardless of Status (<$1k, $1k-250k, etc.) .... and an expression to count (distinct Client).

For the <$1k bucket, I have a special constraint -- I only want to count the clients if they have Status = 'Covered'...

This seems simple, but the tricky part is that a Client can have some Revenue as 'Covered' and some Revenue as 'Uncovered'...my code attached uses a simple 'and' clause to factor in the constraint on Status, but Qlikview doesn't seem to recognize it ... in the attached example, QVW says there are 8 clients <$1k that are 'Covered', but really there are 6....

Anyone able to help?  Thanks!

[#2]

The approach I took was to adjust my dimension to account for 'Status' in the <$1k bucket ... one hunch I have is to ignore 'status' when creating my dimension .. and then introduce some kind of set analysis in my expression which incorporates 'Status' when Revenue <$1k ... but i have struggled to do this properly Any help is appreciated!

[maxgro · Accepted solution]

dimension

=if(aggr(sum(Revenue),Client) >= 250000, '$250k+',

  if(aggr(sum(Revenue),Client) >= 100000, '$100k-250k',

     if(aggr(sum(Revenue),Client) >= 1000, '$1k-100k',

    if(aggr(sum(Revenue),Client)<1000, '<$1k w/ Coverage',

    //and Status='Covered'

        'Other'

))))

expression

=count({$ <Revenue={">1000"}>+<Revenue={"<=1000"},Status={Covered}>} DISTINCT Client)

[#4]

tremendous ... thank you so much ... have never used the union operator before, thanks!!