13 Replies Latest reply: Oct 1, 2013 8:52 AM by Michael Solomovich

# Using the 1 in set analysis but allowing certain selections

Hello everybody,

Does any know a way in set analysis that allows you to use the 1 identifier, but also allow user selections?

I want to calculate Sales over the full set where the Red flag = 1. I then want users to be able to select a region and this to be included.

Something like

SUM({<1{RedFlag={1},Region={\$}>}Sales)

So when a user selects a region this is included in the sum.

Any ideas?

• ###### Re: Using the 1 in set analysis but allowing certain selections

Hi

Try like this

I think, for your scenerio, this give solution

SUM({<RedFlag={1}>}Sales)

• ###### Re: Using the 1 in set analysis but allowing certain selections

Hi

This solution is not using the 1 to select over the whole set.

Thanks for the response.

• ###### Re: Using the 1 in set analysis but allowing certain selections

Maybe like

SUM({<1{RedFlag={1},Region={\$(=GetFieldSelections(Region,','))}>}Sales)

• ###### Re: Using the 1 in set analysis but allowing certain selections

Hi

I can't see what you're saying, but it is not returning a result.

• ###### Re: Using the 1 in set analysis but allowing certain selections

You should get a result at least when selecting in Region.

If you don't select a region, there might be an issue, right, because all regions will be excluded, so you should see zero as result.

You can try something like this:

=sum({1<RedFlag={1} \$(=if(GetSelectedCount( Region ), ', Region = {'& GetFieldSelections(Region) &'}','')) >} Sales)

The dollar sign expansion should evaluate your current selection in Region and - if no selection is made, just return an empty string:

=sum({1<RedFlag={1} >} Sales)

but if a selection is made, eg. North and South, it should return

=sum({1<RedFlag={1}, Region = {North, South} >} Sales)

If your regions look more complicated (i.e. Region values are not just single words), you may need to add quoting, which makes the dollar sign expansion a little more complex.

• ###### Re: Using the 1 in set analysis but allowing certain selections

Try like:

SUM({<RedFlag={1},Field1=, Field2=, Field3=>}Sales)

Here selection in the field Field1, Field2, Field3 would not be taken into consideration but for the field Region  would be, beacuse that is not included in the set expression.

• ###### Re: Using the 1 in set analysis but allowing certain selections

Hi

I can't see what you're saying, but i have lots of fields.

• ###### Re: Using the 1 in set analysis but allowing certain selections

This should work:

SUM({1<RedFlag={1}, Region=P(Region)>} Sales)

Regards,

Michael

• ###### Re: Using the 1 in set analysis but allowing certain selections

Thanks Michael.

I was actually using your other suggestion that you made on another post which involved using a variable and GetFieldSelections

Cheers

• ###### Re: Using the 1 in set analysis but allowing certain selections

Understood..
My suggestion above makes sense only if you actually select a Region.  Later I thought that it is better to use a variable.  Didn't rememebr I'd answered a similar question before... :-)

• ###### Re: Using the 1 in set analysis but allowing certain selections

I can't see what you're saying, but i have lots of fields.

You can let QV do the work to create a list of all fields to clear, have a look into:

Ignore all selections except some specific fields using Set Analysis

• ###### Re: Using the 1 in set analysis but allowing certain selections

If you have many fields, Stefan's solution should work with a little bit of correction, please try :

SUM({1<RedFlag={1},Region={\$(=GetFieldSelections(Region,','))}>}Sales)

• ###### Re: Using the 1 in set analysis but allowing certain selections

As per my knowledge, Set analysis dont allow 1 and selection both together. Obviously you can ignore user selection as per tresesco suggested...

SUM({<RedFlag = {1}, Month = , Year = ,WeekNo = , Qtr =  >}Sales)

Here any selection by user in Month, Year, WeekNo and Qtr will not affect your value but Region selection will give you SUM of SALES for that particular region with RedFlag = 1...