Filtering for Customers Not Purchasing Products Defined by Top N Filter

Question & Answer
, , ,
1

I have a dataset that is sales dimensioned by customer, product and month.

I have 1 pivot table visual showing just the Top N products with a calculated field that sums sales for that product and a Top N parameter/Filter applied to this visual. This allows the user to see the top selling products across all customers within a defined period.

I have a second pivot table visual below. What i would like to display here is a list of all customers who are not purchasing the products in the first visual defined by the Top N filter. I’ve tried to do this with an action to filter the second table with a sales value selection of the first with a filter of null or 0 on the values of the second table. I believe this does not work because i’m inherently filtering out what i’m trying to include by doing it this way.

How can i get a list of customers not buying those products or more importantly have never bought those products? I need to include customers who have never purchased said products, and not just a list of customers who haven’t purchased the selected product within that time frame but have purchased it before. Any advice is appreciated

Thanks!

2

Hello @dspringer1

Welcome to the community! Thanks for your question!

To achieve the desired result in Amazon QuickSight, you can use a combination of calculated fields and custom filtering logic. Here’s a step-by-step approach:

  1. Create a Calculated Field to Identify Top N Products:

  • Go to your dataset and create a new calculated field.

  • This calculated field will identify if a product is in the Top N. For example, name it IsTopNProduct and use the following formula:

  • Example : ifelse(rank([{Sum of Sales}], [{Product}], ASC) <= ${TopN}, 1, 0)

  • Here, ${TopN} is the parameter you use to define the Top N products.

  1. Create a Calculated Field to Check Customer’s Purchase of Top N Products:
  • Create another calculated field that checks if a customer has purchased any of the Top N products. For example, name it HasPurchasedTopNProduct and use the following formula:
  • Example - : sumOver(ifelse([IsTopNProduct] = 1, [Sales], 0), [{Customer}], PRE_AGG) > 0
  1. Create a Filter to Identify Customers Not Buying Top N Products:
  • Now, create a new calculated field to identify customers who have not purchased any of the Top N products. Name it NotPurchasedTopNProducts and use the following formula:
  • Example : ifelse([HasPurchasedTopNProduct], 0, 1)
  1. Create the Second Pivot Table:
  • In your second pivot table visual, add Customer as the dimension.
  • Add a filter to the visual based on the NotPurchasedTopNProducts calculated field, and set the filter to only include customers where NotPurchasedTopNProducts is 1.
  1. Apply the Time Frame Filter:
  • Ensure that both visuals are filtered by the same time frame to maintain consistency.
  1. Optional - Customers Who Have Never Purchased:
  • If you need to ensure the list includes customers who have never purchased those products at all (not just in the defined time frame), you might need to adjust your data preparation process to flag such customers in your dataset beforehand.

By following these steps, your second pivot table should now show a list of customers who have not purchased the Top N products within the defined period, including those who have never purchased those products.

1 Like
3

Hi @dspringer1

It’s been a while since we last heard from you, did you have any additional or follow up questions regarding your initial topic? Were you able to try out the approach that was shared?

If we do not hear back within the next 3 business days, I’ll go ahead and mark the solution to close out/archive the topic.

Thank you!

4

Hi @dspringer1

Since we have not heard back from you with any further information or follow-up questions, I will archive this topic. If you still need assistance, please post a new question in the community and link to this topic to provide relevant information. That will ensure you are at the top of the priority list for a response from one of our QuickSight experts.

Thank you!