2 Replies Latest reply on Apr 13, 2018 5:22 AM by Jim Dehner

    WTD This Year vs WTD Prior Year calculation ?

    ritesh singh

      HIiiiiiii ..... !


      What would be a logic for WTD Previous year. I tried below calculation but it compares DATES not DAYS. 


      based on the date selected in parameter [Select -Date] it calculautes WTD TY and WTD PY

      For example  for Year =2018 WEEK -1 WTD  days included are Feb-1 (thursday) + Feb-2 (friday)       ----- [ considering Fiscal week starts SAT and ends on FRI]


      IF [Orderdate_FY] <= [Select-Date]

      AND DATEDIFF('week',[Orderdate_FY],[Select-Date]) = 0                   --- Works good for This Year WTD

      AND (MONTH([Orderdate_FY])) = MONTH([Select-Date])

      THEN [Sales]



      Similarly, for year = 2017 WEEK -1 WTD previous year  days included must be Feb-2 (Thursday) + Feb-3 (Friday)

      But in below calculation Week-1 of 2017 (Previous year) includes Feb-1, Feb-2, Feb-3. I want only Feb -2 and Feb -3 to be compared in WTD Previous year


      if [Orderdate_FY] <= [Select-Date] - 364

      and DATEDIFF('week',[Orderdate_FY],[Select-Date] -364 ) = 0                          --- What changes do i need to make here  in order to get right comparison?

      AND (MONTH([Orderdate_FY])) = MONTH([Select-Date])

      THEN [Sales]



      This is how my final table looks   -

      ProductWTD-This YearWTD-Previous year


      Any help on this one please .!



        • 1. Re: WTD This Year vs WTD Prior Year calculation ?
          Cathy Bridges

          When you use date part 'week' in a DATEDIFF calculation, it will count in corresponding increments.


          So DATEDIFF('week',...) is going to count weeks, not days.


          It works fine in your first statement with zero weeks difference, but in your second statement you're asking it to give you 364 weeks prior. If you change the -364 to -51 that should do it.

          • 2. Re: WTD This Year vs WTD Prior Year calculation ?
            Jim Dehner

            Good morning


            another way to get at this is through the datetrunc function


            datetrunc('week',today()) returns the date of the first date of the week - so                if datetrunc('week',[order date] ) = datetrunc('week',today()) then [Sales] end



            will sum all the orders that happened this week   - you can look at last week by using dateadd('year',-1,today()) to move the target week back 1 year and then place that in the datetrunc function above


                      if datetrunc('week',[order date] ) = datetrunc('week',dateadd('year',-1,today()))then [Sales] end





            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.