4 Replies Latest reply on Oct 14, 2018 5:15 PM by Jim Dehner

# Rolling 4 week calc based on selected week

Hi everyone,

I've searched the forums and have tried multiple methods to try and get my rolling calc to work with no success so i've put together some sample data hoping someone from here is able to help.

In the attached workbook, i'm trying to calculate a rolling 4 week calc based on the selected filter 'week of week ending' to achieve a 'rolling profit per unit' per employee for the last 4 weeks (inclusive of the one selected). What is the best formula to achieve this? The end goal is for it to be dynamic so we would be able to select a certain week of the year with the rolling calc updating automatically.

The formula I need to use is '(profit 1 + profit 2)/units' and should be expressed as a %. Also, my dataset has hundreds of employees and 30+ weeks so it looks a little more complicated than the one in the sample.

I've tried the following:

• IF (DATEDIFF('week',[Week Ending],TODAY()))<=4 THEN [Profit 1] ELSE null END I was going to create a calculated field for each portion of the formula before aggregating them however it was only pulling 1 week's worth of results.
• WINDOW_SUM(SUM([Profit 1),-3,0) wasn't computing correctly here but I don't feel a table calc is correct here unless I can include a date function in this so it picks up weeks?

Desired result (if week 33 is selected):

Profit 1: - Lisa: 850, - Anne: 700, - Sam: 850

Profit 2: - Lisa: 0, - Anne: 20, - Sam: 0

Units: - Lisa: 35, - Anne: 35, - Sam: 35

Profit per unit: - Lisa: 2428%, - Anne: 2057%, - Sam: 2428%

• ###### 1. Re: Rolling 4 week calc based on selected week

Good morning

see the attached

there were a couple of issues

you were using a filter to "select the end date for your 4 week" - the filter selected a single week - filters filter out all records that don't meet the criteria so you only get the one week

I used a parameter that lets yo still choose a single week - note the set up

then the filter based on the parameter (i used datetrunc)

for the WTD the filter is

for the 4 week rolling it is

then the \$ formula needs to be aggregated

it returns this

and

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.

• ###### 2. Re: Rolling 4 week calc based on selected week

Thanks very much Jim, it worked perfectly. Much appreciated.

• ###### 3. Re: Rolling 4 week calc based on selected week

Would it be best to use a similar 'datetrunc' calculated field to obtain the YTD amount based on the selected parameter control? e.g. if I selected week 28, it would include all data before and inclusive of week 28?

I was thinking the following however it only works for the most recent week..

[Week Ending]>=datetrunc('year',[week end end date parameter])

and

[Week Ending]<=[week end end date parameter]

Thank you

• ###### 4. Re: Rolling 4 week calc based on selected week

Natalie - I just use datetrunc because it returns a date that can be compared to dates in your data set - others use datediff to determine the number of weeks between 2 dates - its all what makes you comfortable

Jim