3 Replies Latest reply on Apr 23, 2018 10:11 AM by Matt Lutton

    Average Date Difference By Sub Cat

    Guillermo Vidaurre



      So my goal is to be able to say customers by X category on average 10 months before they buy Y sub category. Idea being when someone buys X we know to sell them Y 10 months later. I thought it would be similar to Counting customers who Bought both. However, this is not quite working with the date differences. I get why its not working because my LOD is on the wrong partition. However, I am not sure how to fix it. I have attached a file using the Sample Superstore and would love any help. Thanks!

        • 1. Re: Average Date Difference By Sub Cat
          Matt Lutton

          What is the purpose of the Date dimensions that are created via LOD (Sub-Cat Min 1 and Min 2)?  The datediff is using those, but they are not present in the viz.  There is also a reference to a copy of sub-category, and I'm not sure why Sub-Category is shown twice in the viz.


          Can you mock up the desired results, and maybe explain a bit more about the goal so I can better understand?


          Best of luck and happy analyzing!

          • 2. Re: Average Date Difference By Sub Cat
            Guillermo Vidaurre

            Thanks for the response. Here is the mock up in excel I am still using the superstore for my example.


            Below is my data. It shows the name of the customer the sub category and the date they bought that category. Then it shows the days between when they bought each category.


            This is what I want it to look like. So the calculation of Aaron (253) and Adam (172) purchase of Accessories to binders averages to 212.5 as noted below.




            If we did this across a lot of data we could say on average customers buy Accessories 212 days before they buy a Binder.


            Hope this makes sense!

            • 3. Re: Average Date Difference By Sub Cat
              Matt Lutton

              I'm still not sure what the date parameters/dimensions are for (nested inside your DateDiff calc) and am having trouble understanding the work in the TWBX, as well as the requirement you're after.  Hopefully someone else can help!