2 Replies Latest reply on Oct 3, 2018 5:41 AM by R Singh

    Sequential Filtering of data

    R Singh

      I have a database of customer purchases. The dimensions are Purchase_ID, Customer_ID, Date and Product.

       

      Each line of the database represents a unique purchase (with Purchase_ID as identifier). Each purchase is of a single product.

      Product is the product which was purchased - one of 20 different products the company sells.

      Customer_ID is the customer identifier - A customer may have made multiple purchases on different dates, each purchase made by him will have the same Customer_ID.

      Date is the date of purchase.

       

       

      What I want to do - Filter out those customers who purchased Product X in date range A AND also purchased about Product Y in date range B.

       

      (For example, I want to find all customers who  purchased product "Contact Lens" in Aug'18 AND product "Lens Solution" in Sep'18 - to target for specific sales promotions etc).

       

      Simply put, a chart or dashboard view where the user will be able to select 4 inputs - Product A, Date Range X, Product B, Date Range Y. The output should be the graph showing the total number of customers who have purchased Product A in Date Range X AND product B in Date Range Y.

       

      (I understand this is tricky since the database is structured around purchases whereas my filter criteria center around customers...I have no clue how to approach this. Any help will therefore be greatly appreciated. Thanks!!!)

        • 1. Re: Sequential Filtering of data
          Joe Oppelt

          Make parameters for the 4 items.  Then you can make LOD calcs that look at the various criteria for each Customer.

           

          { FIXED [Customer_ID] : SUM(  IF MONTH(Date) = [Month Param 1] and [Product] = [Product Param 1] then 1 END ) }

           

          (Note:  this is NOT exact syntax.  You'll need to figure out the specific data types and spellings based on your data setup!)

           

          If that calc is greater than zero then your customer bought item-1 in month-1.

           

          Make the same or item2 and month2.

           

          If both are greater than zero then that's a customer you want to target.

          1 of 1 people found this helpful
          • 2. Re: Sequential Filtering of data
            R Singh

            Thanks Joe! This is very helpful. I didn't know about the option of creating parameters, looked it up after your answer - it makes this very simple. (For other Tableau novices reading this, you can refer to Create Parameters for more info on parameters, which is a really powerful feature).

             

            Thanks again!