topic Set Analysis - ignore selections except for one field in QlikView

Set Analysis - ignore selections except for one field

Wed, 09 Oct 2013 14:13:18 GMT

Looking at the following set analysis we see that we are summing the Sales field over the current selections ignoring any selections made to the Year field.

Sum ( {$ <Year = > } Sales)

I want to do the opposite. I want to sum all sales, ignoring any selections although I do want it to honor selections made on the Year field (and only the year field).

I know that the following will give me the sum of all sales ignoring selections. How do I modify this to honor selections on a single field only?

Sum ( {1 } Sales)

Thanks!

Ken

Re: Set Analysis - ignore selections except for one field

Wed, 09 Oct 2013 14:20:56 GMT

I figured out one solution to this which is fairly simple, although I'm not sure if it's the absolute best solution.

Sum ( {1 <Year={"$(=Year)"}>} Sales)

Re: Set Analysis - ignore selections except for one field

CELAMBARASAN — Wed, 09 Oct 2013 14:20:58 GMT

You can use something like

=Sum({<$(=Concat({<[$Field]-={'Year'}>}[$Field], '=,'))>} Sales)

Re: Set Analysis - ignore selections except for one field

MayilVahanan — Wed, 09 Oct 2013 14:27:44 GMT

Please refer this

Ignore all selections except some specific fields using Set Analysis

Re: Set Analysis - ignore selections except for one field

pgrenier — Wed, 09 Oct 2013 14:30:33 GMT

Hello Ken,

You may want to try this:

Sum({1<Year=$::Year>} Sales)

Cheers,

Philippe

P.S. Attached is an example QVW file with an example

Re: Set Analysis - ignore selections except for one field

mikecrengland — Wed, 09 Oct 2013 14:51:25 GMT

Hi Ken -

I went the rounds with this a few months ago, and I think this works:

Sum({1<[Segment Name] = {$(=If(GetSelectedCount([Segment Name]) = 0,

Chr(39) & '*' & Chr(39),

Concat (Distinct Chr(39) & [Segment Name] & Chr(39),',')))}

>}[Net Price])

It looks a little crazy, but I found that Concat didn't behave like I thought it would. If anybody's willing, I'd love you to read through my thought process on this at Fortune Cookie BI: The One .

Mike