Match against multiple criteria, ignoring filter etc...

[Anonymous]

Hi everyone,

I have been going nuts trying to figure this one out - any thoughts would be most welcome...

I have my sample data below and attached in a screen shot, my source data is on the left, what I'm trying to produce is on the right.

Brief explanation:

I insert a filter and select 'Leo', the results I want are as shown. Basically, I want to see who else bought something on the same day from the same location. I've shown the results in what looks like a pivot table style, but I'm not wedded to the idea.

I tried doing a pivot table (is there a way to make all the collapsed rows auto expand???), but this didn't work as when I filtered the customer name, it then stopped showing the other names in the results...help please...

Purchase Date	Customer	Location of Item	Item
21/01/2017	Leo	Leeds	iPhone 7
21/01/2017	Kate	Leeds	iPhone X
21/01/2017	Bob	Leeds	ipad Mini
28/01/2017	Sarah	Newcastle	iPhone 7
29/01/2017	Leo	Manchester	Galaxy
29/01/2017	Sarah	Manchester	Xbox One
02/02/2017	Bob	Bolton	ipad Mini
02/02/2017	Sarah	Bolton	Galaxy
06/02/2017	Bob	Leeds	iPhone X
06/02/2017	Sarah	Leeds	iPhone X
10/02/2017	David	Manchester	Galaxy

Desired results:

Filter selection: Leo
21/01/2017	Leeds	Kate
		Bob
29/01/2017	Manchester	Sarah
Filter selection: Sarah
29/01/2017	Manchester	Leo
02/02/2017	Bolton	Bob
06/02/2017	Leeds	Bob
Filter selection: Bob
21/01/2017	Leeds	Leo
02/02/2017	Bolton	Sarah
06/02/2017	Leeds	Sarah
Filter selection: David
-

cheers,

Pokes

[sunny_talwar]

May be this

=If(GetSelectedCount(Customer) = 1, Only({<[Purchase Date] = p([Purchase Date]), Customer = e(Customer)>} Customer), Customer)

[petter · Accepted solution]

Something like this:

[Anonymous]

Hi Sunny,

Sorry for the late reply - I'm currently using my Apple Mac so can't check your solution with Qlik, but your formula seems to do the trick - much appreciated.

[Anonymous]

Hi Petter,

This works an absolute charm - many thanks.

Rgds,

Pokes...