Dynamic Rename Columns and Sum Data between various Date Ranges

Question

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

New Workflow1.yxmd

Community file.xlsx

DataNath · Accepted Answer

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.

binu_acs · Accepted Answer

@Smatthews33 One way of doing this

Smatthews33 · Accepted Answer

Thanks very much DataNath.

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