Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

pokermon
New Contributor II

Match against multiple criteria, ignoring filter etc...

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 DateCustomerLocation of ItemItem
21/01/2017LeoLeedsiPhone 7
21/01/2017KateLeedsiPhone X
21/01/2017BobLeedsipad Mini
28/01/2017SarahNewcastleiPhone 7
29/01/2017LeoManchesterGalaxy
29/01/2017SarahManchesterXbox One
02/02/2017BobBoltonipad Mini
02/02/2017SarahBoltonGalaxy
06/02/2017BobLeedsiPhone X
06/02/2017SarahLeedsiPhone X
10/02/2017DavidManchesterGalaxy

Desired results:

    

Filter selection: Leo
21/01/2017LeedsKate
Bob
29/01/2017ManchesterSarah
Filter selection: Sarah
29/01/2017ManchesterLeo
02/02/2017BoltonBob
06/02/2017LeedsBob
Filter selection: Bob
21/01/2017LeedsLeo
02/02/2017BoltonSarah
06/02/2017LeedsSarah
Filter selection: David
-

cheers,

Pokes

1 Solution

Accepted Solutions
MVP
MVP

Re: Match against multiple criteria, ignoring filter etc...

Something like this:

2017-12-01 02_57_59-Qlik Sense Desktop.png

4 Replies

Re: Match against multiple criteria, ignoring filter etc...

May be this

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

MVP
MVP

Re: Match against multiple criteria, ignoring filter etc...

Something like this:

2017-12-01 02_57_59-Qlik Sense Desktop.png

pokermon
New Contributor II

Re: Match against multiple criteria, ignoring filter etc...

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.

pokermon
New Contributor II

Re: Match against multiple criteria, ignoring filter etc...

Hi Petter,

This works an absolute charm - many thanks.

Rgds,

Pokes...