8 Replies Latest reply on Feb 4, 2019 7:35 PM by Hideki OGAWA

    Creating a common baseline

    Jamie Hubler

      Hello all,

       

      I am having trouble creating a common baseline.  I've looked out Tableau's Toy Story example and tried to set my data up like that, however, I have no field that gives me number of days.  Attached is an example of my data, I have many more fields, however, I  believe this is all that should be needed for this calculation.  I want a line chart that gives a line for every new product group, that starts at the date active (1) and proceeds, by # of month from there.  Any suggestions on how I can make this work?

       

      I know I am missing something really simple here, but can't wrap my head around it.

       

      Thank you!

       

      Jamie

        • 1. Re: Creating a common baseline
          Hideki OGAWA

          Hi Jamie,

           

          I guess this is the same topic as this,

          Creating a calculated baseline

          but this time, you already have 'date active' for each product in your data.

           

          Then calculate # of months from active date like this;

          DATEDIFF('month',[Date Active],[Invoice Date])

          And put this in 'dimension' and use as X axis.

           

          Attached is the example of cummulative sales by this.

          I hope this is what you need.

           

           

          Best regards,

          Hideki

          1 of 1 people found this helpful
          • 2. Re: Creating a common baseline
            Rodrigo Calloni

            Hi Jamie

             

            I think the sample workbook only has the first day for each product group so it wasn't possible to see if the solution I propose fully works.

             

            The solution has two parts. One to create a INDEX() based on the Date Active, so that we normalize the first day of any year as index =1.

             

            Then I had to change the SUM(Sales) to another Table Calculation, now using Difference. Why difference? Because each start date has a different value for Sales. So if you want all of them to start at the same point, that point needs to be 0 and then you calculate the difference starting from there.

             

            But if you only do a Difference table calculation you will get a Null for the first point (there is no difference to calculate if you have only one point). So I've added a IFNULL on that Table Calculation and it now shows Index 1 with all dots at the same starting point, 0 (you can click on the color legend to confirm that all dots are there).

             

            Screen Shot 2019-01-27 at 10.52.35 PM.png


            I hope when you connect this to more data you will have the lines you expect to see.

             

             

            I hope this helps (workbook is attached).

             

            Rodrigo

            1 of 1 people found this helpful
            • 3. Re: Creating a common baseline
              Jamie Hubler

              Unfortunately, I cannot open your workbook.  I get an error that the workbook is saved in a newer version of Tableau.  Below I took your advise, but am not getting the results.  I think I may have my difference calculation different.  Below is my formula and Table calc for the Week # (don't get stuck on the label, this was copied from toy story.  I will probably rename to Month #)

               

               

              The Difference calc I ended up with is the below:

               

              I then put  week # on columns and Calc2 on Rows.  I get the below:

               

              I am having trouble figuring out where to go to from here.

               

               

              @ Mention

               

              • 4. Re: Creating a common baseline
                Jamie Hubler

                Thank you Hideki,

                 

                I can't seem to open the workbook you provided.  It seems I would need a newer version of Tableau to do so.  I did look at the link you provided, and it seems Matt Lupton did exactly what I am trying to do.  I replied to his post as well, to see if I can figure out what I am doing wrong. 

                 

                Thanks for your help.  I will mark this once I figure this out!

                • 5. Re: Creating a common baseline
                  Hideki OGAWA

                  Hi Jamie,

                   

                  Glad to help you, but please note there's a difference between his data and yours.

                  His data doesn't have 'Date Active' and define the first sales date appeared in the data for each product as day 1.

                  Your 'Data Active' is not necessarily the first sales date (some product sold months later than Date Active for the first time).

                   

                  For your case, you should use DATEDIFF, instead of INDEX of sales date (or FIXED LOD should work as well).

                   

                   

                  Best regards,

                  Hideki

                  • 6. Re: Creating a common baseline
                    Jamie Hubler

                    Thanks for the clarification Hideki.  I am getting closer.  See attached.  One thing I am noticing is that the datediff calc is not starting everything at 0.  I can't figure out why this didn't work.  Any suggestions?

                    • 7. Re: Creating a common baseline
                      Jamie Hubler

                      Actually both responses helped me.  Hideki helped with the calculation and Rodrigo helped with the calculation to get Sales to plot correctly.  Thank you everyone!  This has been an issue for a couple of months and I finally figured it out!

                      • 8. Re: Creating a common baseline
                        Hideki OGAWA

                        Sorry for my late reply.

                         

                        The reason why some products start from 'not 0' is because the data is like that.

                        Taking New Product 3 as an example, month of Date Active is Feb 2016, but first invoice month in this data is Feb 2018.

                        That is why New Product 3 starts from 24.

                         

                        If you want to use the first month of invoice date of each product, you should calculate that instead of Data Active,

                         

                        First invoice date by product (LOD)

                        { FIXED [New Product Group]:MIN([Invoice Date])}

                         

                        then take month difference from each invoice date

                         

                        Months from First invoice date

                        DATEDIFF('month',[First invoice date by product (LOD)],[Invoice Date])

                         

                        Please see 'test' sheet for your review on the attached file (v2018.1) so you can see how these calculation are different.

                        If you need anything else, please let me know.

                         

                         

                        Best regards,

                        Hideki