Data in chart, ignoring selection

[richard_chilvers]

I have table of items bought by customers, with a sequence number to indicate 1st, 2nd 3rd..... purchase:

Customer, Sequence, Item

PX1,1,Boots

PX1,2, Coat

PX1,3, Shirt

PX2,1,Coat

PX2, 2, Handbag

PX3, 1, Coat

PX3,2, Scarf

PX3, 3, Hat

I want to show a chart which lists customers' 1st and 2nd purchases. But I want to show the user a list of items. From the list they will select a 1st purchased item (eg. Coat) and then list all customers who purchased this item first. But crucially I want to also show what they purchased 2nd.

I feel this is simple, but I'm struggling.

[sunny_talwar · Accepted solution]

This?

Dimension: Customer

Expressions

1) =Only({<Sequence = {1}>}Item)

2) =If(Only({<Sequence = {1}>}Item) > 0, Only({<Sequence = {2}, Item>}Item))

[richard_chilvers]

Thanks - very helpful. It would've taken me a while to get to that !

[richard_chilvers]

I have a further request. When a 1st item type is selected, I want to show a bar chart counting the number of each type of item which is the 2nd purchase.

How would I write dimension/expression?

Thanks

[sunny_talwar]

Not sure how this will behave on your actual data, but can you try this:

[richard_chilvers]

Mmmm. This gives inconsistent results compared to manually checking results from part1 of your solution. But its a good start.

Thank you.

[sunny_talwar]

Would you be able to guide me toward a case where we get inconsistent result? I might not have fully understood the requirement