Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

Thank you for your help,

Aurore

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hi,


Can you try this:

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

View solution in original post

18 Replies
kkkumar82
Specialist III
Specialist III

Hi

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

kkkumar82
Specialist III
Specialist III

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

Anonymous
Not applicable
Author

Here is a sample :

   

BUSalesManAmount sold
AJohn1 000
ASteve10
AMarc20
BJohn100
BSteve1500
BMarc2000

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:
Load * Inline [
BU,SalesMan,AmountSold
A,John,1000
A,Steve,10
A,Marc,20
B,John,100
B,Steve,1500
B,Marc,2000
];

Employee:
Load * Inline [
SalesMan,BU_Employee
John,A
Steve,B
Marc,B
];

OmarBenSalem

Try this?

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

But you have to select BU and SalesMan

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

Thanks but that won't do.

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

Anonymous
Not applicable
Author

Hi there,

have you tried aggregation?

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

Hope that helps

OmarBenSalem

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

See the image below:

Capture.PNG

kkkumar82
Specialist III
Specialist III

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

if dimensions are taken as BU and Salesman