Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Something like this:
May be this
=If(GetSelectedCount(Customer) = 1, Only({<[Purchase Date] = p([Purchase Date]), Customer = e(Customer)>} Customer), Customer)
Something like this:
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.
Hi Petter,
This works an absolute charm - many thanks.
Rgds,
Pokes...