Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Consider the following table:
Identifier | Risk | Color | Shape | Amount |
---|---|---|---|---|
10 | High | Red | Circle | 12 |
10 | Low | Green | Circle | 31 |
20 | Low | Blue | Square | 57 |
20 | Medium | Purple | Triangle | 32 |
590 | Medium | Purple | Octagon | 90 |
590 | Medium | Blue | Trapezoid | 83 |
43 | Medium | Red | Circle | 23 |
43 | Low | Red | Octagon | 53 |
43 | High | Blue | Octagon | 78 |
43 | Low | Yellow | Trapezoid | 14 |
90 | High | Orange | Triangle | 47 |
90 | High | Yellow | Triangle | 85 |
13 | Medium | Green | Rectangle | 45 |
48 | Medium | Brown | Rectangle | 63 |
48 | Medium | Brown | Oval | 56 |
48 | Low | Green | Oval | 60 |
Set the following filters:
Identifier = 590
Risk = Medium
Color = Purple
I would like to SUM the "Amount" column, but when performing the sum, consider all filters except the Identifier filter. The correct result of the sum would be 122. How would I do this in a set based query?
Thanks Carleton
So then you can use
This will ignore any selection made for Identifier
This would work:
Sum({<Color = {'Purple'}, Risk = {'Medium'}, Identifier = >}Amount)
But I agree with Garret that it is a little confusing. Maybe if you give us some more detail we can find another solution.
Matt
So maybe like this
SUM({<Identifier=,Color ={'Purple'},Risk ={'Medium'}>}Amount)
Please see the attached QVW
I agree that a little more context here would be helpful. In the example I gave, the user happened to filter on Risk and Color, however, we won't know ahead of time what columns or values they've filtered on.
We only know that we want to consider all filters except a filter applied to the Identifier field. Here's sudo code of what I think the solution would be:
SUM ({ Apply all filters, except ignore the Identifier filter }Amount)
I would use this all over the place! I will create visualizations that allow the user to setup peer groups (via filtering) to compare against an individual value.
For example: How does Identifier 10 compare against the overall group of High risk peers? Sure, I could hard code this comparison, but I want to give the flexibility to the user to choose the peer group via the filters.
Thanks Carleton
So then you can use
This will ignore any selection made for Identifier
This was exactly what I was looking for, thanks a ton Alan!
your welcome pal!