formula to get data within a time frame

Question

Hello,

The excel spreadsheet is what I am hoping to create in Alteryx. I'm looking for a formula that can pull in the Column D if last payment date is still valid. (not sure how to pose what I am looking for.)

For example, Cell E5, the last payment date is January 2022. So it is valid for Column H (october 2021 - sept 2022). or Cell E8, the last payment date is Jan 2023, so it is valid for Column H, Column I but not valid for column J.

Hope that makes sense!

Pleas help!

jessy_chow · Answer

Once I summarize, how do I pull in the data to the respective columns?

That's the output in Alteryx in my workflow. So I would like to summary the data into the columns shown below per the examples provided in the discussion. Hope that makes sense. Thanks!!

jessy_chow · Answer

Once I summarize, how do I pull in the data into their respective columns?

Tony_Castillo · Answer

Tough to give a solution in one go without seeing the raw data under the pivot table. If you don't have a specific payment day and are relying only on month and year, then you may need to setup a reference column that uses DateTimeMonth([payment date]) and DateTimeYear([payment date]). Once this is identified for each record, then you can use the Summarize-> Group By [new reference field]  and Summarize-> Sum [Monthly Annual Rate]