18 Replies Latest reply: May 17, 2017 3:10 AM by aurore bec okab

# Set Analysis - filtering a flied based on selections on another field

Hi,

I'm having trouble writing a set analysis. I want to display the revenues generated by the people from a Business Unit (no matter if it is generated in another business unit) when said business unit is selected.

In other words :

if my current selection is BU = "A" , then I want sales generated by BU A Salesmen to appear, even if it was generated in BU B

Set analysis should be around : sum( { \$ < BU= , BU_Employee= {BU} >}sales)

but I don't seem to be able to make it work.

Sorry, i can't post my data, it's in french and pretty much tangled with a lot of other info.

Aurore

• ###### Re: Set Analysis - filtering a flied based on selections on another field

Hi

Can you create a sample data atleast 5 to 6 and explain your scenario, it helps to understand the issue

• ###### Re: Set Analysis - filtering a flied based on selections on another field

Here is a sample :

 BU SalesMan Amount sold A John 1 000 A Steve 10 A Marc 20 B John 100 B Steve 1500 B Marc 2000

John is from BU (business unit) A, Steve and Marc are from unit B. ==> This data figures in field "BU_Employee"

When I select BU "A", I want the amount sold 1100 (1000 in unit A + 100 in unit B) to be displayed.

Is that any clearer ?

----

Update : Here is the script for better understanding :

Sales:
BU,SalesMan,AmountSold
A,John,1000
A,Steve,10
A,Marc,20
B,John,100
B,Steve,1500
B,Marc,2000
];

Employee:
SalesMan,BU_Employee
John,A
Steve,B
Marc,B
];

• ###### Re: Set Analysis - filtering a flied based on selections on another field

Try this?

Sum({<BU=P({<BU>}) >}AmountSold)

But you have to select BU and SalesMan

• ###### Re: Set Analysis - filtering a flied based on selections on another field

Thanks but that won't do.

I need to not select SalesMan, as I need to present Sales per Business Unit in another graph.

• ###### Re: Set Analysis - filtering a flied based on selections on another field

The expression will work if you have as dimension BU or SalesMan:

See the image below:

• ###### Re: Set Analysis - filtering a flied based on selections on another field

Thanks. I does show proper results per salesman.

However, I cannot plot the same graph with the BU which my businessmen are from (the field is named "BU_Employee")

• ###### Re: Set Analysis - filtering a flied based on selections on another field

Hi there,

have you tried aggregation?

Sum(aggr( sum(AmountSold), SalesMan))

Hope that helps

• ###### Re: Set Analysis - filtering a flied based on selections on another field

Thanks,

I tried Sum(aggr( sum(AmountSold), BU_Employee)), it shows exactly what I want.

However, i loose all the details per salesman, so it's not ideal.

• ###### Re: Set Analysis - filtering a flied based on selections on another field

I see, what details do you lose and where? What chart does it work in and what one does it not?

• ###### Re: Set Analysis - filtering a flied based on selections on another field

If I chart your solution, it work like a charm :

But I put it in a cross table, not so much

• ###### Re: Set Analysis - filtering a flied based on selections on another field

Well if information is a pivot table; you can keep my measure if you do as follow:

• ###### Re: Set Analysis - filtering a flied based on selections on another field

Hi,

Can you try this:

=sum({<BU=, BU_Employee={'\$(=GetFieldSelections(BU))'}>}AmountSold)

• ###### Re: Set Analysis - filtering a flied based on selections on another field

It works perfectly !!

Thank you very much

• ###### Re: Set Analysis - filtering a flied based on selections on another field

If I understand correctly are you saying that Selected BU is A but in that Salesman for BU B also done business, something like that

• ###### Re: Set Analysis - filtering a flied based on selections on another field

Yes,

Also Salesman from A did business in A & B. I want sales done by Businessman from A when A is selected, regardless where sales were actually made.

• ###### Re: Set Analysis - filtering a flied based on selections on another field

Sum({1<BU = P(Bu)>}Sales)

if dimensions are taken as BU and Salesman

• ###### Re: Set Analysis - filtering a flied based on selections on another field

Thanks, but as I've said I can't select BusinessMan.

I've updated my model for better comprehension.

• ###### Re: Set Analysis - filtering a flied based on selections on another field

is it something like you select a salesman and if he is present in a BU you want to see the Salesman selected + other

salesman who also associated with the BU which the selected salesman is , then my expression would work