Dynamic Range Sum by date

[Tom216]

Hi All,

I have been attempting to do a dynamic range sum on my data set by date range but to no avail.

I need to accumulate the previous for 4 weeks values for each week. I have achieved this by using the multi-row formula.

The issue I have is when there are weeks missing in my data set.

	Value	Required Output
Week 1	10
Week 2	20
Week 3	5
Week 4	40	75
Week 5	10	75
Week 7	5	55
Week 8	30	45
Week 9	20	55
Week 10	50	105

The required output column is the current week plus the previous 3 weeks values.  So week 4's output is week1:Value + week2:Value + week3:Value + week4:Value.

The problem is when I try and calculate weeks 7, 8 and 9 because week 6 is missing.

I need to show that week 6 is missing so in this case I need to sum the weeks that are present in the 4 week window.

So week 7's calculation would be week4:Value + week5:Value + week7:Value.

Currently the multi-row expression I am using is [Row-3]+[Row-2]+[Row-1]+[Row].

Any ideas?

Thanks,

Tom

[patrick_digan · Accepted solution]

@Tom216 can your week number be boiled down to a number? If so, I would try something like this in a multi-row tool:

IIF([Row-3:Week]<[Week]-3,0,[Row-3:Value])+
IIF([Row-2:Week]<[Week]-3,0,[Row-2:Value])+
IIF([Row-1:Week]<[Week]-3,0,[Row-1:Value])+
[Value]

[jasperlch]

Hi @Tom216, 

The Moving Summarize macro developed by CReW would make the task very simple (no coding/formula needed):

[neilgallen]

@Tom216 One other option would be to use the generate rows tool, creating one record for every week between your minimum and maximum date. Join back to your original data and you now don't have missing weeks. See the attached workflow.

[Tom216]

Thanks all for your solutions. 

I think I left out some details.  The weeks are quarter weeks so after week 13 they starts at 1 again.  

Week 12

Week 13

Week 1

Week 2 

etc....

Also the example data I provided is not how it appears in the data set,  The null row for week 6 does not exist. 

So it really looks like this:

	Value	Required Output
Week 1	10
Week 2	20
Week 3	5
Week 4	40	75
Week 5	10	75
Week 7	5	55
Week 8	30	45
Week 9	20	55
Week 10	50	105

Apologies for the confusion.

@patrick_digan Yes this is what I am after.  Thanks!

@jasperlch  great macro but  does not take into account the missing weeks.  Just sums the last 3 rows.

@neilgallen this would work except I don't want to populate every week.