Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Dynamic Range Sum by date

Tom216
7 - Meteor

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.

 

 

 ValueRequired Output
Week 110 
Week 220 
Week 35 
Week 44075
Week 51075
   
Week 7555
Week 83045
Week 92055
Week 1050105

 

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

4 REPLIES 4
patrick_digan
17 - Castor
17 - Castor

@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
12 - Quasar

Hi @Tom216

 

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

 

Capture1.PNG

 

 

 

 

 

 

 

 

neilgallen
12 - Quasar

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

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:

 

 

 ValueRequired Output
Week 110 
Week 220 
Week 35 
Week 44075
Week 51075
Week 7555
Week 83045
Week 92055
Week 1050105

 

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.

Labels