Set Analysis - ignore selections except for one field

[#1]

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

[#2]

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)

[CELAMBARASAN]

You can use something like

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

[MayilVahanan]

HI

Please refer this

Ignore all selections except some specific fields using Set Analysis

[pgrenier · Accepted solution]

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

[mikecrengland]

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

www.fortunecookiebi.com