Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Set Analysis Challenge

Consider the following table:

IdentifierRiskColorShapeAmount
10HighRedCircle12
10LowGreenCircle31
20LowBlueSquare57
20MediumPurpleTriangle32
590MediumPurpleOctagon90
590MediumBlueTrapezoid83
43MediumRedCircle23
43LowRedOctagon53
43HighBlueOctagon78
43LowYellowTrapezoid14
90HighOrangeTriangle47
90HighYellowTriangle85
13MediumGreenRectangle45
48MediumBrownRectangle63
48MediumBrownOval56
48LowGreenOval60

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?

1 Solution

Accepted Solutions
Highlighted
Master II
Master II

Thanks Carleton

So then you can use

2013-11-01_2253.png

This will ignore any selection made for Identifier

View solution in original post

6 Replies
Highlighted
Not applicable

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

Master II
Master II

So maybe like this

SUM({<Identifier=,Color ={'Purple'},Risk ={'Medium'}>}Amount)

Please see the attached QVW

Highlighted
Not applicable

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.

Highlighted
Master II
Master II

Thanks Carleton

So then you can use

2013-11-01_2253.png

This will ignore any selection made for Identifier

View solution in original post

Highlighted
Not applicable

This was exactly what I was looking for, thanks a ton Alan!

Highlighted
Master II
Master II

your welcome pal!