QlikView App Dev

[leefulford]

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

[kaushiknsolanki]

HI,

Can you share sample data along with expected output.

Regards,

Kaushik Solanki

[leefulford]

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]

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

[leefulford]

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]

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.

Hope this helps

Juraj

[kaushiknsolanki]

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

[devarasu07]

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