2 Replies Latest reply on Apr 4, 2017 2:51 PM by GoogleMac

    Same Date Last 1/2/3 Year(s) Ago

    NickUlrich

      Title

      Same Date Last 1/2/3 Year(s) Ago

      Post

           Hey all,

           I need to calculate the same day of the week from a prior year to compare the revenue generated on those days... Is this simply a matter of Date-364, Date-728, Date-1092, etc, etc? I would effectively want to compare the same Monday's vs Monday's, Tuesday's vs Tuesday's, etc.

           Theoretically, this makes sense to me, but comparing the WeekOfYear, it seems to break down, the further back I would go.

           Hoping somebody has some thoughts or advice on this.

           Thanks in advance!

        • 1. Re: Same Date Last 1/2/3 Year(s) Ago
          TKnTexas

               Yes this makes sense... same day sales for retail.  I create dates fields for last year, last month, etc.  I set up relationships from curr_date to the one for the relationships I am creating.  It was one of the first relationships I learned back in Version3.  Very cool.  I hope that helps get you started.

          • 2. Re: Same Date Last 1/2/3 Year(s) Ago
            GoogleMac

            I needed the same thing calculated and was able to figure it out! There may be a simpler way to do it, but I could not find it. For those wondering why it ISN'T just - 364 is because we are looking for the same DAY OF THE WEEK as last year, to compare sales.

             

            Here is the calculation for 1 year back. To get x years back, replace the -1 on the first two rows with -x.

             

             

            // beginning of last year MINUS the day of week of Jan 1st of last year

            // PLUS the amount of weeks of the current year *7 (number of days)

            // PLUS the day of the week of the current year

             

            Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) -1 )

             

            - DayOfWeek ( Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) -1 ) )

             

            + ( WeekOfYear ( Get ( CurrentDate ) ) *7 )

             

            + DayOfWeek ( Get ( CurrentDate )  )