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

Summing records based on Start and End Date

cireost
8 - Asteroid

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!

7 REPLIES 7
tcroberts
12 - Quasar

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

 

sumbasedondate.PNG

 

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

 

Let me know if this helps, 

 

Cheers!

 

 

ponraj
13 - Pulsar

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. 

 

WorkflowWorkflowResultsResults

cireost
8 - Asteroid

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

 

sumbasedondate.PNG

 

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.

tcroberts
12 - Quasar

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?

cireost
8 - Asteroid

Hi tcroberts - building on this, I have a couple scenarios I am trying to address.  I have attached the workflow with the Input and Data tables and results.  The goal is to return the total payment from the data table based on the input table.  Here is what I am trying to accomplish below:

 

Sample 1 - OK

 

Sample 2 - End Date from Input Table is beyond the Data Table.   However, it returned one extra month based on the expression written.  How can we address this?

 

Sample 3 - Start Date from Input Table is before the Data Table.  However, it still returned two months.  I would like it to return a null to allow user to know that they entered an incorrect Start Date.

 

Sample 4 - If someone fat fingered the start date and type 1/2/2017 instead of 1/1/2017, it returns an additional month.   What could be a solution to this?

 

 

 

 

tcroberts
12 - Quasar

For your second and third examples, it appears that there's no way to directly limit the Date Input Tool, but the solution provided here guides another user through stopping the workflow with an error. You could perhaps use the test tool instead so you can pass a summarized version of the dataframe into it as well containing the min and max days. 

 

As for the last one, I'm not sure I understand, are you saying the error was in the Start Date input from your app, or in the data?

 

If in the app, you could parse out the year and month in alteryx using something like DateTimeFormat([StartDate], '%Y-%m')

 

or whatever the appropriate format would be, then do:

 

DateTimeParse([MonthYear] + "-01", '%Y-%m-%d')

 

which would set it to the first day of the month, and presumably not pull in the extra month.

 

Let me know if this works or you'd like me to try and make a sample workflow,

 

Cheers!

cireost
8 - Asteroid

For my specific situation, I won't be able to utilize an app for the Input Table.  Meaning, the Input Table is populated by a user and then its run through the workflow.  So for Sample 2 and 3 - because either the start or end dates per the Input Table are before or beyond the dates in the Data Table, it is returning results that may not be accurate based on the current expression in the Formula Tool. 


Similarly, for Sample 4 (sorry for the confusion before but I fixed it in the Input Table now).  But if user types 1/2/17 - 6/30/17, it returned 5 months instead 6.  Same result would occur if someone typed 1/15/17 or anything but the first of the month in this situation. 

 

It would appear to only address these is if the user knew exactly when the start or end dates were, but that would be impossible in my situation.  Not sure if there is a way to edit the Formula expression to address any or all of these situations. or rather, some combination of a formula or filter tool prior to the join function?


And as always, any example changes to the workflow attached would be greatly appreciated!

 

Thanks!

Labels