4 Replies Latest reply on Oct 18, 2016 2:32 AM by chris.sutcliffe

    Counts YoY (current day over same day last year)

    chris.sutcliffe

      Hi everyone,

       

      I'm having a really tough time trying to figure out how to show the below data with the final intention of creating a bar graph with a YoY overlay line.

       

      So for example:

       

      sales dateTotalSame DAY Last YearTotal Same DAY last Year
      2016-10-1810202015-10-20990

        

      So I basically want to:

      1. count the total based on the current date

      2. count the total based on the current dates same day last year.

       

      the sales date is in the data and the same day last year is created using a calculated field but I can't for the life of me figure out how to produce the counts.

       

      If anyone can point me in the right direction that would be awesome.

       

      Chris

        • 1. Re: Counts YoY (current day over same day last year)
          Sherzodbek Ibragimov

          Hi Chris

          try to this tread as they mentioned solution for it with sample data. I hope it helps

           

          Re: The same day last year comp

          • 2. Re: Counts YoY (current day over same day last year)
            chris.sutcliffe

            Hi there,

             

            Thanks for the link, I have already attempted going these solutions to help with my problem but none seem to fit the exact problem I have. If anyone has any other suggestions I would really appreciate it!

             

            The logic I have in my head to solve this is something like this:

             

            IF Sales Date = 2016-10-18 THEN SUM Sales WHERE Sales Date = 2015-10-20.

             

            Basically I want to take the sales date and only count the sales from the same day in the previous year. I have the date calculation down just don't know how to link the counts.

             

            Thanks in advance!

             

            Chris

            • 3. Re: Counts YoY (current day over same day last year)
              praveen p

              Hi Chris,

               

              Please try the below formula which might help

               

              Sales today and same day last year:

               

              if DATEDIFF('day',[Order Date],TODAY())=0 or

              DATEDIFF('day',[Order Date],TODAY())=366 then [Sales]

              end

               

              If you want to make the dates static you can write something like below

               

              if [Order Date]= #18-10-2016#  or [Order Date]= #20-10-2015# then [Sales]

              end

               

              -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

              you can use parameter in the place of dates and make it dynamic

               

              if [Order Date]= #Date parameter1#  or [Order Date]= #Date parameter2# then [Sales]

              end

               

              Thanks

              1 of 1 people found this helpful
              • 4. Re: Counts YoY (current day over same day last year)
                chris.sutcliffe

                Hi Praveen,

                 

                Thanks, i'm not sure if I'm implementing this correctly. It seems to just give me two values and not on the same line, for example:

                 

                sales dateSame DAY last yearSales
                2015-10-202014-10-20990
                .......
                2016-10-162015-10-18
                2016-10-172015-10-19
                2016-10-182015-10-201020

                 

                It might be because I'm forced to wrap the DATEDIFF in an ATTR function so its stops complaining about mixing aggregate and non aggregates (my sales is another calculated field using COUNTD)

                 

                Which is ALMOST there, I just need to be able to do it for all dates and be on the same line, for example

                 

                 

                sales datesame day last yearSalessales same day last year
                2016-10-182015-10-201020990

                 

                Once I have these nailed down I can then calculate my YoY growth

                 

                Thanks again for the help.