topic Re: Set Analysis Challenge in QlikView

Set Analysis Challenge

Fri, 01 Nov 2013 21:38:51 GMT

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?

Re: Set Analysis Challenge

Fri, 01 Nov 2013 22:00:08 GMT

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

Re: Set Analysis Challenge

rustyfishbones — Fri, 01 Nov 2013 22:02:11 GMT

So maybe like this

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

Please see the attached QVW

Re: Set Analysis Challenge

Fri, 01 Nov 2013 22:57:54 GMT

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.

Re: Set Analysis Challenge

rustyfishbones — Fri, 01 Nov 2013 23:05:14 GMT

Thanks Carleton

So then you can use

This will ignore any selection made for Identifier

Re: Set Analysis Challenge

Fri, 01 Nov 2013 23:19:36 GMT

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

Re: Set Analysis Challenge

rustyfishbones — Fri, 01 Nov 2013 23:20:35 GMT

your welcome pal!