topic Re: Get values of a dimension for a current selection in App Development

Get values of a dimension for a current selection

Wed, 10 May 2017 11:21:26 GMT

I am trying to access the values for a specific dimension for the current selection so that I can include them in an expression. I have come across GetFieldSelections but I think this only returns values for a dimension if it is actually used in the selection itself.

I need to do this for the following use case:

I have salespeople assigned to teams within an office which in turn is assigned to a region. If I select a region and and then a sales team I would like to return the following in a table/chart (example numbers):

1) Number of salespeople in team selected = 6

2) Number of salespeople in region (for team selected) = 125

3) Number of salespeople in office (for team selected) = 30

1) is simply count(distinct salesperson_id)

2) can be obtained by using GetFieldSelections as the field region was used in the selection

3) is my challenge as I want to retrieve the offices that are part of the selected region (or alternatively put the offices for the region that the team ultimately selected belongs to).

Any pointers very welcome!

Can I use the P() operator?

Re: Get values of a dimension for a current selection

swuehl — Wed, 10 May 2017 11:32:57 GMT

Shouldn't this be just plain associative logic, the default Qlik behaviour, if your tables are connected properly?

Re: Get values of a dimension for a current selection

vinieme12 — Wed, 10 May 2017 11:41:24 GMT

3) Number of salespeople in office (for team selected) = 30

Try below

ignore selections made in salespersonid or salespersonname

count({<salesperson_id=,salespersonname=,Region = p(Region)>}distinct salesperson_id)

Re: Get values of a dimension for a current selection

Wed, 10 May 2017 11:43:04 GMT

I don't think so.

Lets say I have 5 regions and I have 50 offices in those regions and 200 teams in those 50 offices.

Lets also say I have a box with a simple expression: count(distinct salesperson_ID)

I would like a box alongside this one that tells me how many salespeople there are in the office(s) that are part of the selection.

For example say I select NorthWest for the region and then select Team78. My box mentioned above would tell me that there are 6 people in Team78. The box alongside it I want to tell me how many people there are in Manchester which happens to be the office that Team78 belongs to. If I could retrieve from the selection of Team78 that the office value is always Manchester I would then be able to reference that in the expression for the box I want!!

Any clearer?

Re: Get values of a dimension for a current selection

vinieme12 — Wed, 10 May 2017 11:46:05 GMT

It'd be easier for people here to assist if you can post a sample data with expected output

Preparing examples for Upload - Reduction and Data Scrambling

Re: Get values of a dimension for a current selection

Wed, 10 May 2017 11:46:46 GMT

I tried that one but it gives me the same count - ie. if I select an individual salesperson it returns 1 whereas I want it to return the number of salespeople that are in the region that the salesperson belongs to!

Re: Get values of a dimension for a current selection

vinieme12 — Wed, 10 May 2017 11:50:26 GMT

can you post the expression you used? did you add the fieldnames to be ignored?

Re: Get values of a dimension for a current selection

Wed, 10 May 2017 11:54:22 GMT

OK:

salesperson_ID,region,office,team

1,NorthWest,Manchester,Team78

2,NorthWest,Manchester,Team78

3,NorthWest,Manchester,Team78

4,NorthWest,Manchester,Team34

5,NorthWest,Manchester,Team23

6,NorthWest,Liverpool,Team56

7,NorthWest,Liverpool,Team56

8,London,City,Team01

9,London,City,Team01

10,London,City,Team02

If I have selected "NorthWest" and then salesperson_ID=3 then:

count(distinct salesperson_ID) returns 1

I want an expression that returns 5 (ie. the number of salespeople in Manchester; the office which salesperson_ID=3 belongs to....

Re: Get values of a dimension for a current selection

Wed, 10 May 2017 11:58:40 GMT

Also if I selected Team78 to start with then:

count(distinct salesperson_ID) returns 3

I still want an expression that returns 5 (ie. the number of salespeople in Manchester, the office which Team78 belongs to...

Re: Get values of a dimension for a current selection

Wed, 10 May 2017 12:01:02 GMT

I tried:

count({<OFFICE=P(OFFICE)>}distinct SALESPERSON_ID)

It gives me 1 if I have an individual sales person selected; ie. the same as count(distinct SALESPERSON_ID)

Do I need to include ALL fields that should be ignored?

Re: Get values of a dimension for a current selection

swuehl — Wed, 10 May 2017 12:05:13 GMT

Count({1<office=p() >} DISTINCT salesperson_ID)

Re: Get values of a dimension for a current selection

Wed, 10 May 2017 12:07:39 GMT

You superstar - that is spot on!!

Re: Get values of a dimension for a current selection

vinieme12 — Wed, 10 May 2017 13:06:36 GMT

yes , mostly the field that you will be making selections on,

please note adding {1< will ignore all selections

http://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/ChartFunctions/SetAnalysis/set-modifiers.htm