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....
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!