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

    Counts YoY (current day over same day last year)


      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


      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.



        • 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)

            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!



            • 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]



              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]




              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]




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

                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


                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


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


                Thanks again for the help.