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

Summarize Dates within Range

Associate_1121
7 - Meteor

Hello,

 

I am having trouble figuring out how to do this. Essentially, I have changing ranges in one column. Every individual date has a count as either a 0 or a 1. I want to sum all of the counts within the selected range. For example, if the range is Jan 1-Jan 5, then the new column would give a total of 3 in the attached picture.

 

 Capture.PNG

14 REPLIES 14
Associate_1121
7 - Meteor

Yes, everything in the picture is in the input file. The range is the current date +1.5 years. The output will need to look through all data within that range and give a summary of the count.

Associate_1121
7 - Meteor

This is the full input file.

CharlieS
17 - Castor
17 - Castor

Since these are all 1.5 year ranges, any date range entered should be contained within that meaning all records should be summed every time. In case that changes, I've built out the attached solution that should give you all the necessary tools to get this done.

 

Associate_1121
7 - Meteor

The date range changes because the start date is different for each row. Since it moves up every day, there needs to be different sums for the different ranges. 

 

For example, on January 1, the next 1.5 years should be summed. However, on January 2, January 1 should be excluded.

jdunkerley79
ACE Emeritus
ACE Emeritus

If I have understood correctly, you want the total of Count when Start Date is in the Range specified.

 

If this is correct, I suggest:

2018-12-11_19-09-31.png

 

First create a running total

Next convert so a complete sequence of dates (start and end)

Make it unique in case a date in both start and end (making sure the union put start before end)

Use a multi-row to copy down the running totals

 

You can then join to get the end date running total

Finally a formula tool to compute totals

 

Hope this is what you meant 

Sample attached

Labels