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

"And" operator in set analysis using the same dimension.

Hello all,

Hoping someone could help me out here. I am trying to have a pivot table that would show all customers that a selected Sales rep has called that another rep has called as well, and then show the call history of the other reps.

So say Employee 1 has called customers A, B, C.

Employee 2 has called customers B and C and D.

Employee 3 has called customer B and E.

I want the list to show customers B and C, the other employees that have called them and the call history (number of calls per month)

I am unsure how to accomplish this in set analysis. As I need it to first filter the table based on the selected employee having called them ($(vEmpSelect) , but also only show those where another employee has called them.

This gives me calls of the selected employee where an event_Id = a call.

Count({<Employee={'$(vEmpSelect)'}, [Year-Month] ={">=$(vYearStartPerm)"}>}DISTINCT EVENT_ID)

If anyone could help me out with this that would be fantastic, I've been stuck for over a day.

Cheers,

Lee

7 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

HI,

Can you share sample data along with expected output.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable
Author

Hello,

Yes here is some sample data:

Event Id, Employee, Customer

000001, EmpA, 1

000002, EmpA, 2

000003, EmpA, 3

000004, Emp B, 2

000005, Emp B, 4

000006, Emp C, 2

000006, Emp C, 3

One employee will always be selected. Suppose EmpA is selected. What I want in my table is:

Customer, Employee, Number of Calls

2, Emp B, 1

2, Emp C, 1

3, Emp C, 1

Because customers 2 & 3 have been called by employees other than EmpA.

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Try this,

Create a straight table with 2 dimension Customer and Employee.

Now your expression will be

Count({<Employee = e({<Customer = p(Customer)>}),Customer = p(Customer)>}[Event Id])

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable
Author

This is awesome! Thank you! I'm not exactly sure how this works but I am thankful!

If I wanted to add another set analysis so MonthYear = $(vMaxMonthYear) where would I put it?

Thanks a lot,

Lee

juraj_misina
Luminary Alumni
Luminary Alumni

Hi Lee,

this one was fun. What you need is

Count({<Customer=P({$}), Employee=E({$}Employee)>} [Event Id])

This will select customers which are possible given the current selection of EmpA, but at the same select all employees excluded by the selection of EmpA.

community_PE_Set.PNG

Hope this helps

Juraj

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

It depends on your requirement.

If you want to find the customers whom employee had called in that month then it should be with in p() and e() else it can be outside.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
devarasu07
Master II
Master II

Hi,

Check the below thread, u can get more clear idea about indirect set analysis(note:  P() possible & E() exclude elements works with in set analysis.

P() &amp; E() and where do you use them?

Thanks,

Deva