3 Replies Latest reply on Feb 18, 2017 10:42 AM by Jamieson Christian

    Product Voids For Customers With Specific Date Ranges

    Mat Henning

      Hello all!

       

      Here is my situation that I am having difficulty solving myself (this is using Super Store Data)......

       

      • I need to generate a list of accounts that haven't purchased "X" product with a specific date range - IE 1/1/2017 through 2/11/2017. There will be multiple products with multiple date ranges.
      • I need to be able to cross reference that list and show what what customers purchased "X" product within another date range - IE 2/12/2017 through 4/1/2017
      • On top of that, I need to be able to assign a points value for each specific item so a sales rep knows how much "At risk" (unearned points) and how much "Earned" (filling a "void" item to a customer that didn't buy a specific product in the prior date range).

       

      Thank you in advance for your help on this matter!

       

      Mat

        • 1. Re: Product Voids For Customers With Specific Date Ranges
          Jamieson Christian

          Mat,

           

          Do you have a sample (e.g. Excel mockup) of what your final output should look like? That might help me understand your requirements a bit better. I'm a little hazy on what exactly the user should be entering (parameters), what data is intrinsic to products (secondary data source), and what level of detail should be shown to the user. (All accounts with a single aggregate score? A single account with a list of scores per product?)

           

          If you have a workbook showing your progress so far, that would also be helpful. At least it will give more clues as to what you're angling toward.

           

          Thanks!

          • 2. Re: Product Voids For Customers With Specific Date Ranges
            Mat Henning

            I'm thinking that the easiest way might be to create another data source (like attached) to cross reference from instead of writing several lines of code per product......

             

            • With this example Tableau would search/return any store that hasn't purchased either product between 1/1/2017 and 1/31/2017
              • It doesn't matter if the store has purchased one, both, or neither, but the product(s) that weren't purchased needs to be identified

             

            As I was thinking more about it, there will be examples where the same product may have multiple instances through out the year with different values, so I added an "Event Name" to identify a different tracking period/point schedule for the sales reps.

             

            The LOD the end user should see is the individual items that a store *hasn't* bought in the given time period for the "event," along with the potential point value. Should a rep sell that particular account that specific item within the "void filled" range they should earn those points and be able to see that as well.

             

            Let me know if this helps you know where I am going with this or if I need to provide more specific information!

             

            Thank you in advance!

             

            Mat

            • 3. Re: Product Voids For Customers With Specific Date Ranges
              Jamieson Christian

              Mat,

               

              Here's an example solution using Superstore, a modified version of your Void Example specifications, and Tableau 10 cross-datasource joins. (Workbook is in version 10.1.4)

               

              • I used "Sub-Category" in Superstore to be the "Product ID". (I actually renamed "Sub-Category" to "Product", so you'll see that in the screenshots.)
              • I used "Order Date" as the measure of when qualifying purchases happened.
              • Results are measured for each "Customer Name".

               

              Here's what I set up:

               

              1. Pull in Superstore, then click Add and locate the Excel file that contains your Void Event specifications. Add it to the data source to create a cross-datasource join on Sub-Category (which I already renamed to "Product" in the screenshot below) and Product ID. (This will result in too many records being joined, but we'll use a more complex LOD Expression to filter out the ones that don't matter.)

               

              2. Create a calculated field [Customer Qualifies for Event] like so:

               

              3. Create a calculated field [Customer Void Filled] like so:

               

              4. You can use [Customer Qualifies for Event] to filter out all of the records that are irrelevant to the reporting, by filtering only on TRUE. (I've set it up as a Dimension filter in my example, but you can actually use it as a Data Source filter, too.)

               

              5. You can use [Customer Void Filled] to report on whether a particular customer has had their purchase void filled in the qualifying period.

               

              Here is an example of the resulting data. I included some [Sales] data, by [Order Date] (month) so that it's easy to see that the period prior to the Void Fill event has no sales, and how [Customer Void Filled] = TRUE if the period of the Void Fill event has sales.

               

              I hope this example, and the attached workbook, help you work out the solution that you're working on.