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

# WTD This Year vs WTD Prior Year calculation ?

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]

END

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]

END

This is how my final table looks   -

ProductWTD-This YearWTD-Previous year
A\$\$
B\$\$
C\$\$

Any help on this one please .!

Thanks

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

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 ?

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

Jim

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.