topic Re: Using the 1 in set analysis but allowing certain selections in QlikView

Using the 1 in set analysis but allowing certain selections

Anonymous — Tue, 01 Oct 2013 09:50:19 GMT

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

MayilVahanan — Tue, 01 Oct 2013 09:53:46 GMT

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

swuehl — Tue, 01 Oct 2013 09:55:02 GMT

Maybe like

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

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

tresB — Tue, 01 Oct 2013 09:58:23 GMT

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

Anonymous — Tue, 01 Oct 2013 10:06:11 GMT

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

Anonymous — Tue, 01 Oct 2013 10:07:06 GMT

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

Thanks for the reply.

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

Anonymous — Tue, 01 Oct 2013 10:07:50 GMT

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

Thanks for the reply.

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

MK_QSL — Tue, 01 Oct 2013 10:10:55 GMT

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...

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

tresB — Tue, 01 Oct 2013 10:20:22 GMT

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

swuehl — Tue, 01 Oct 2013 10:22:20 GMT

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

Anonymous — Tue, 01 Oct 2013 10:28:13 GMT

This should work:

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

Regards,

Michael

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

swuehl — Tue, 01 Oct 2013 11:35:46 GMT

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

Anonymous — Tue, 01 Oct 2013 12:36:26 GMT

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

Anonymous — Tue, 01 Oct 2013 12:52:02 GMT

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... 🙂