9 Replies Latest reply on Jul 23, 2013 12:15 PM by Jonathan Drummey

    Summarizing point in time data

    T Ops



      I have a challenging use case for data analysis that I am really hoping Tableau can help with. Here is my issue in a nutshell:


      I have a detail level Oracle data table that contains purchase details for our customers. Here is an example row in that table:


      UserID | purchaseDate | purchaseID | purchaseAmount | retailLocation


      I have been asked by my company's executive team to categorize our users (found in UserID) into different segments. In particular, I am looking at figuring out which users are repeat buyers. A "repeat buyer" is defined by input taken from the user. So, I have two parameters:


      Number of Purchases (min number of purchases per month)

      Number of Months (min number of months)


      So, say for example we have the values of Number of Purchases = 2 and Number of Months = 5. That means that we are defining a Repeat Buyer as someone who as of the given month, has made 2 or more purchases per month for the last 5 months.


      Once I have calculated who my Repeat Buyers are, I then want to plot these counts over time. So, I'd like to have a crosstab that looks like:


      Jan 2013 | Feb 2013 | Mar 2013 | Apr 2013 | May 2013

      123         | 54           | 456         | 321         | 136


      Which can be read as "As of Mar 2013, we have 456 users who had made 2 or more purchases per month for the last 5 months". This is the part I am struggling with. I am not sure how to calculate this count for a certain point in time. I am thinking something like a WINDOW_SUM but for a given start and end month would work. Is there such a function?


      Any help you can offer to get to the above crosstab will be much appreciated.


      Thanks in advance,



        • 1. Re: Summarizing point in time data
          Jonathan Drummey

          See the attached for one option. I used the superstore sales data with # of Records being the # of purchases. It uses a couple of parameters to drive the view. The MY(Order Date) field also has Show Missing Values to pad out the data so that we can look across dates where a given customer didn't have any purchases.


          Of the three calculated fields, two are nested table calculations, the Repeat Buyer Flag uses a Compute Using of Order Date, that's nested within the # of Repeat Buyers calc that has a Compute Using of Customer. A copy of the # of Repeat Buyers pill is on the Filters Shelf to filter for non-Null values to optimize the display.



          • 2. Re: Summarizing point in time data
            T Ops

            Hi Jonathan,


            Thank you very much! This is SO helpful. Exactly what I needed. I've implemented in my own workbook with our data and it works like a dream. I really can't thank you enough for your expertise and quick response!





            • 3. Re: Summarizing point in time data
              Jonathan Drummey

              You're welcome!

              • 4. Re: Summarizing point in time data
                T Ops

                Hi again Jonathan,


                Thank you again for your help on this question. I've done a lot with your solution! One follow up question for you if its not too much work:


                So now I'd like to allow the user to see, for a given month who are the repeat buyers? This means I'd have an additional parameter called "Segment Month" that would allow the user to select 1 month/yyyy date value. Then I would see a crosstab view that would show me customer ids. I've been trying to figure it out on my own, but of course when I take the date pill and move it down to the detail pane, it messes everything up.


                Any help you can offer is very much appreciated!


                Thanks, Sarah

                • 5. Re: Re: Summarizing point in time data
                  Jonathan Drummey

                  See the attached. The first view won't work by moving pills around because the optimized calc only returns one value per date (that's the IF FIRST() ==0 in the calc). I set up a separate view that uses the Repeat Buyer Flag to filter the customers for just the repeat buyers, and a table calculation filter to choose the date. You could use a parameter filter (which would have more performance), the advantage to the table calc filter for date is that it's totally dynamic.



                  1 of 1 people found this helpful
                  • 6. Re: Re: Summarizing point in time data
                    T Ops

                    You are a Godsend! Thank you, this is very helpful. I've done a lot with your method to the point where I know have several segments of users that I can see totals for as well as detail data.


                    One final question for you (I hope) : On my detail worksheet where I can see the repeat buyer details for a given month, I have a number of quick filters that I am allowing the user to filter on. This will trim down the repeat buyers for the month based on what the user selects. What I'm struggling with is how to show the user the new "total" based on their filters. This is similar to the total that is shown at the bottom left hand side in the screen that reads "n marks". N will change depending on the user's selection and I want to show that in the dashboard if possible. I can't seem to figure it out, so I thought I would see if you've come across this before.


                    Thank you again!



                    • 7. Re: Re: Re: Summarizing point in time data
                      Jonathan Drummey

                      Hi Sarah,


                      In the attached I used the # of repeat buyers calc on the Level of Detail Shelf, set up the Compute using appropriately, and then added that to the Title.

                      • 8. Re: Re: Re: Summarizing point in time data
                        T Ops

                        Thanks for your quick response Jonathan! I tried this, but instead of the Sum of the AGG() it brings up the Min to Max. I'm not sure why its doing that or how I can change it to the Sum, there doesn't seem to be an obvious way to select which aggregation to show.

                        • 9. Re: Re: Re: Summarizing point in time data
                          Jonathan Drummey

                          I'm thinking that your compute using settings are not the same. The # of Repeat Buyers calc has a nested Compute Using setting. The inner Repeat Buyer Flag has a Compute Using of Order Date, while # of Repeat Buyers has a Compute Using of Customer Name.