Summing records based on Start and End Date

Hi - I am fairly new to Alteryx. I have the following fields: Tenant name, Start Date, End Date, and Payment. My inputs are: Tenant Name, Start Date, and End Date and output is Payment.

I am trying to find a tool or write a formula that will add each row in Payment beginning on Start Date and ending on End Date.

Please see example attached.

Thanks!

Summing based on dates.xlsx

Expression

Accepted answers

tcroberts

You probably want an Analytic App with inputs for Start and End Dates that will update a filter. Here's a sample workflow:

You could add further inputs to filter the Tenants as well, or whatever else you need.

Let me know if this helps,

Cheers!

sumbasedondates.yxwz

tcroberts

You could join that new table to your existing one on Tenant name, and then write the filter expression as:

[Start_Date] >= [Right_Start_Date] AND [End_Date] <= [Right_End_Date]

where the 'Right_' fields are those ones coming from the table of start/end dates you want to look at that you're joining in.

Would that solution work for your use-case, or are there other complications I've overlooked?

All comments

tcroberts

You probably want an Analytic App with inputs for Start and End Dates that will update a filter. Here's a sample workflow:

You could add further inputs to filter the Tenants as well, or whatever else you need.

Let me know if this helps,

Cheers!

sumbasedondates.yxwz

ponraj

Here is the sample workflow for your case. Hope this is helpful. I dont see any input entry for payment. Hence, I kept it blank and did not do any sum.

Workflow Results Results

Summing records based on Start and End Date.yxmd

cireost

@tcroberts wrote:
You probably want an Analytic App with inputs for Start and End Dates that will update a filter. Here's a sample workflow:
You could add further inputs to filter the Tenants as well, or whatever else you need.
Let me know if this helps,
Cheers!

Thanks for the quick response! Unfortunately, I cannot open the workflow as I have an older version Alteryx. However, based on the screenshots, I understand what you did. Is there another solution without using an Analytic App to update the filter? The Tenant Name, Start and End dates inputs will come from another table.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703