Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
evason75
Contributor

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?


1 Solution

Accepted Solutions
mov
Esteemed Contributor III

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

This should work:

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

Regards,

Michael


13 Replies

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)

MVP
MVP

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

Maybe like

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

MVP
MVP

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.

evason75
Contributor

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.

evason75
Contributor

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.

Thanks for the reply.

evason75
Contributor

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.

Thanks for the reply.

MVP
MVP

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

MVP
MVP

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)

MVP
MVP

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.

Community Browser