Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Not applicable

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

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!

1 Solution

Accepted Solutions
MVP
MVP

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

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)

3 Replies
Not applicable

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

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!

MVP
MVP

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

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)

Not applicable

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

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