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

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

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

kaushiknsolanki — Thu, 25 Jan 2018 20:30:59 GMT

HI,

Can you share sample data along with expected output.

Regards,

Kaushik Solanki

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

Anonymous — Thu, 25 Jan 2018 20:49:16 GMT

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.

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

kaushiknsolanki — Thu, 25 Jan 2018 21:08:40 GMT

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

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

Anonymous — Thu, 25 Jan 2018 21:15:39 GMT

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

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

juraj_misina — Thu, 25 Jan 2018 21:27:56 GMT

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

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

kaushiknsolanki — Fri, 26 Jan 2018 05:46:40 GMT

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

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

devarasu07 — Fri, 26 Jan 2018 07:19:17 GMT

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() & E() and where do you use them?

Thanks,

Deva