3 Replies Latest reply on Oct 3, 2018 2:36 PM by Joe Oppelt

    Comparing YTD vs Prior Years

    Jaden Prottsman

      I'm trying create a view that compares YTD order quantities for current year vs prior years.

       

      Essentially I'm looking to compare order quantities on October 3 for the the past 3 years. This query updates daily so I would like it to be dynamic and have been toying with the use of TODAY in calcs but have been unable to find a solution.

       

      The example below shows what I have, currently for October it compares data as of 10/31/17 &10/31/16 vs 10/3/2018

       

       

       

      Steven Nesteby

      Spencer Swartz

        • 1. Re: Comparing YTD vs Prior Years
          Joe Oppelt

          I'm not sure what your question is.

           

          I guess you have put YEAR in the color shelf to get the three lines.  That's a good way to go.

           

          Do you want your graph to chop off the lines at the last current-year date in your data?  (Chop off the other two years as of October 3 as well, and end the chart as of today.)  Or maybe you just want to chop off the current-year line so that you don't get a straight line from today to the end of the year.

           

          There are a lot of things you can do with this.  If you upload a sample workbook I can work with you to do some of them.

          • 2. Re: Comparing YTD vs Prior Years
            Jaden Prottsman

            Joe,

             

            I have testing year (group) on the color shelf to identify the 3 lines.

             

            My goal is to have the graph end at current date and get rid of the long trailing orange line for all future dates.

             

            I've added both a discrete Month and Day to the columns shelf to get the correct overlay but i still have the long trailing orange line which reflects today's value for each day in the future. I've attempted to add a filter with [Creation Date]<=TODAY() but that doesn't work on the discrete date points I've included.

             

            My requested solution would be either of the following:

            1) Cut off the trailing orange line from the viz below so that each day it updates the orange line would grow.

            2) Cut off the entire viz each day at a discrete TODAY

             

             

            • 3. Re: Comparing YTD vs Prior Years
              Joe Oppelt

              It would be easier to show you in a workbook than to describe things without and example.

               

              I've done both the solutions you've suggested.  For instance, I'm guessing (I have to keep guessing without a workbook!) that you're doing a running sum of some sort to generate the lines.  Your running sum for the current year can force a null when you are at a date past TODAY().  That would end the line at TODAY().

               

              Likewise your running sums for the prior years could force nulls when the date is greater than DATEADD('year',-1,TODAY()) and less than MAKEDATE(YEAR(TODAY()-1,12,31).  (And the same for -2.)

               

              DATEADD adds the specified time period and the specified amount to the date given.  So -1 says to add negative one year to TODAY.  And MAKEDATE makes a date value from the given year, month, day.  So those two chunks of syntax used today would act on dates from Oct 3, 2017 through December 31, 2017.