Get values of a dimension for a current selection

[#1]

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?

[swuehl]

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

[vinieme12]

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)

[#4]

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?

[vinieme12]

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

[#6]

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!

[vinieme12]

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

[#8]

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

[#9]

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

[#10]

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?