Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
abo_saretec
Contributor II
Contributor II

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
yddona_lyn
Contributor III
Contributor III

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

abo_saretec
Contributor II
Contributor II
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

abo_saretec
Contributor II
Contributor II
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.

abo_saretec
Contributor II
Contributor II
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.

mattquinnterex
Creator II
Creator II

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