7 Replies Latest reply on Feb 1, 2019 12:32 AM by Simon Runc

    How to make Promo Period chart: multi-year, days from date, running total

    Caleb Bross

      I pretty much have no idea what to do to make this (I've researched and tried lots of things but nothing works right). I need to make a line chart that looks something like this:

      20190130_104559.jpg

      Each year there is a promo period. The start and end dates vary. I want to show on the x-axis the number of days from the end of the promo with a running total sales $ for each year. Each year has a different length of days for the promo period so the start of the line will differ. I've attached a workbook with fake data (and a lot less) in a spreadsheet. (I normally connect to a database and have millions of rows)

      spreadsheet.PNG

      One thing to note is there are sales after the promo period of that year that shouldn't be included. Here are the promo end dates:

      2017  3/8/2017

      2018  3/1/2018

      2019 3/6/2019

       

      EDIT:

      The first date of that year will always be in that year's promo window. I made a calculation just taking 2017 into account (filtered to only 2017)

      DATEDIFF("day", Date([Datetime]), #2017-03-08#) and I think that part works and I made the following chart:

      chart.PNG

      However, I can't seem to reverse the running total. I've looked it up and nothing seems to work correctly. I replaced w/ the updated workbook.

      I'd appreciate any help or any recommended resources! I'm using Desktop 2018.3.

        • 1. Re: How to make Promo Period chart: multi-year, days from date, running total
          Simon Runc

          hi Caleb,

           

          So the key to this is to create a common baseline for each year. I've broken this down into stages, but you can nest this all in one (or 2) calculations if you wish.

           

          First we need to get the First Date for each year. We can do this with an LoD expression

           

          [First Date of Each Years Promos]

          {FIXED [Year]: MIN([Datetime])}

           

          Then we can use this to work out how many days from this start each day is...

          [Promo Days]

          DATEDIFF('day',[First Date of Each Years Promos],[Datetime])

           

          We can now use this as our common baseline (so for all years Promo Days = 0 is the first day, and so on.

           

          Finally we can plot this on a line chart, and use the "quick table calculation" option to get a running sum

           

           

           

          Hope that is what you were after and all makes sense.

           

          btw if you want to filter out certain dates, just filter them out and everything should update to reflect what is unfiltered (unless you are removing dates which would be the start, in that case you'd need to also add the date filter to be "in context" so it affects out first LoD, which gets the first date)

          2 of 2 people found this helpful
          • 2. Re: How to make Promo Period chart: multi-year, days from date, running total
            Jim Dehner

            Good morning

            see the attached

            the start and end dates are hard coded

            then used in filters set to true

             

            and the promo days are  - convert to discrete dimension

             

            and make the viz

             

             

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            2 of 2 people found this helpful
            • 3. Re: How to make Promo Period chart: multi-year, days from date, running total
              Caleb Bross

              Hi Simon Runc,

              Thanks for your reply! I'm going to check it out. But for some reason I can't open the packaged workbook you've attached. Error(160,66): no declaration found for element 'simple-id' and so on. I have Desktop 2018.3.0 so not sure if that's the issue.

              • 4. Re: How to make Promo Period chart: multi-year, days from date, running total
                Simon Runc

                oops my bad! I accidentally did it on the 2019 Beta!

                 

                Here you go...in an actually released version!!

                3 of 3 people found this helpful
                • 5. Re: How to make Promo Period chart: multi-year, days from date, running total
                  Simon Runc

                  hi Vanessa,

                   

                  Glad it helped. With regards your second question, if you change the LoD expression from a MIN to a MAX it will now ensure that the promos end on the same date, rather than start on the same date (I've renamed the field from First Date... to Last Date...

                  [Last Date of Each Years Promos]

                  {FIXED [Year]: MAX([Datetime])}

                   

                  There are other ways you can specify the running sum to work in reverse, but will this does the trick, so no need to complicate things!

                   

                  On your first question ...In Tableau (like many things) there are lots of ways to achieve the same thing, and which is best depends on the situation (and sometimes no one way is "better"). I guess mine has the advantage of being more dynamic, in that the last promo date is picked up in the formula, but as you have to hard-type the Promo End Date anyway,  Jim's will be more efficient as mine has to perform an LoD calculation, which has some computational cost. You won't really tell the difference unless you get into the 10s and 100s of millions of rows of data.

                   

                  I've attached an updated version and put a formula in to filter out the non-needed dates (which I did have to make a "context filter" so it affects the {FIXED [Year]: MAX([Datetime])} formula

                  1 of 1 people found this helpful
                  • 6. Re: How to make Promo Period chart: multi-year, days from date, running total
                    Caleb Bross

                    Thanks Simon Runc! Since I can't reverse the running total, I'm trying to figure out how to make it look like what the bosses want. Is there a way in Tableau to edit the axis numbers so the negative sign isn't showing? Or maybe I need to make a calculated field to find the reversed running total rather than using the table calculation?

                     

                    EDIT:

                    I figured it out I think. I right clicked the axis, chose format, then changed the number format to custom #,##0;#,##0

                    It takes away the negative sign.

                    • 7. Re: How to make Promo Period chart: multi-year, days from date, running total
                      Simon Runc

                      Ah those pesky bosses and their wants!!

                       

                      Cool. Yes that's the way I'd suggest. We can specify the Running Sum to run in the other direction, but why work harder!

                       

                      In fact you could go with a custom formatting of

                       

                      "";#,##0;#,##0

                       

                      which would remove the positive numbers

                      1 of 1 people found this helpful