Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

"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
Highlighted
MVP & Luminary
MVP & Luminary

HI,

Can you share sample data along with expected output.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Contributor
Contributor

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.

Highlighted
MVP & Luminary
MVP & Luminary

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Contributor
Contributor

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

Highlighted
Luminary
Luminary

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
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