Summarize Dates within Range

[Associate_1121]

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.

 

[JordanB]

Hi @Associate_1121

I have attached an example of how you can build these business rules and aggregate the data. I am not sure what your ranges are so I can't build anything dynamic, but this should give you a good idea.

Best,

Jordan

[Associate_1121]

Hi @JordanB,

Thank you for your response. Perhaps I explained it incorrectly. The ranges actually change for each row. The ranges are included in the row. Is it possible to get totals for the range specified in that row by adding all rows with dates that fall within that range?

[CharlieS]

I'm a little confused as well. How does the table in your first post give a result of 3 and where would that value be applied?

In your first example, if Jan 1-5 is input, then the possible date included are either 1-5 or 2-4 depending if you include the 1st and 5th or not. If it's 1-5, then the 1-2,2-3,3-4,4-5 rows would yield a result of 4. If it's 2-4, then the 2-3,3-4 rows would yield 2. 

---

[Associate_1121]

Hi @CharlieS 

I need the dates to be inclusive. The formula would not need be counting the count column, but rather summing the values. It would be 0+0+1+1+1.

[CharlieS]

What does the "0+0+1+1+1" represent in you example? Let's break this down into steps so we can understand the process, then we'll get to a solution.

[Associate_1121]

Hi Charlie,

The 0+0+1+1+1 represents the sum of the "Count" column which fall into the date range of Jan 1- Jan 5.

Essentially, the count of 1 represents "Yes" while the count of 0 represents "No". I already converted the Yes/No into a value of 0 or 1.

The range changes (see range column). The range is inclusive (January 1-January 5 would include January 1st and January 5th). Each row has a date and an end date. This constitutes the range for each row. I need to know the sum of the counts for every row that has a Date that falls within that range. In the picture above, row 1 has a range from Jan 1, 2015 through July 2, 2016. I need a separate output column to tell me how many "Yes" there are (indicated by a "1" so that we can use Sum or Count), which have a date within that range.

[CharlieS]

The date ranges you provided in your example are over a year in length (ex: 2015-01-01 to 2016-07-02), they all contain the dates Jan 1 - Jan 5. Shouldn't all of the rows be Count=1 ?

[Associate_1121]

Sorry, the count is based on different data (a yes/no question previously answered). Each individual date is either marked as yes or no and therefore now has a 0 or 1. The picture is what the input file will look like. I want to be able to put this in and then the output will tell me how many yes responses there are within the range. The data set continues on for 4,000 rows.

[CharlieS]

So the Input table of Range|Count will be an input file, and the user will select a date range to sum the Count column within? Will the user select Year/Month/Day or just Month/Day?