Dynamic Rename Columns and Sum Data between various Date Ranges

[Smatthews33]

Hello Alteryx Community,

I'm trying to figure out a way for Alteryx to total columns between two dates (start date and month end date) similar to column Q below. The start date is never the same so the date range varies on each row.  The date headings below run to Jan-23 but in reality they will keep extending further each month.  Is anyone able to help me with this please?

Is there a way to format the heading Jan-25 as a date?  Alteryx does not appear to be able to do this.  I can do this in Excel and perform calculations on the date headings.  In Alteryx, it seems that I can only format cells as a date under a heading.  Please see my file attached named "community file". It shows my Alteryx output plus the formula that I would like to see in column Q.

Thanks for your support

Sarah

[DataNath · Accepted solution]

Hey @Smatthews33, how does this look? The workflow initially transposes the table and brings your headers into the dataset itself, so that they can then be referenced in the filter that checks whether or not they're within the date range. Those that are are then summed before being joined back to the raw data, along with those that fell outside of the range being assigned 0. I've left your original 'desired' output field in the flow I've built so you can see the output matches, but please let me know if there are any issues when you apply this to a wider dataset.

EDIT: Have also included the correctly-formatted headers, with the addition of the Dynamic Rename tool which checks whether or not the field name is a date and converts in into Mon-YY format if so.

[binuacs · Accepted solution]

@Smatthews33 One way of doing this

[Smatthews33 · Accepted solution]

Thanks very much DataNath. 

This is amazing, I thought it was impossible.  I will build this into my larger workflow.

[DataNath]

No problem at all @Smatthews33! Feel free to mark the post as the solution so others can find it easily in future if they have the same query! If you need any further help then let us know and can revisit!

[Smatthews33]

Many Thanks binuacs.  This solution works very well. thanks for your quick response.