Dynamic Range Sum by date

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

Workflow

Accepted answers

patrick_digan

@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]

All comments

patrick_digan

@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):

New Workflow1.yxzp

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.

Running 4 week total with missing week.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703