Need assistance with a Multi-Row formula configuration

Hello-

I am seeking a bit of assistance with configuring the Multi-Row formula, which is what I believe I will need to accomplish the following:

In the below sample I'd like to add a new field which would total the 'Sum Buys' column for that VLL ID (column A) IF the Trade Date (column falls between the 'Trade Date' value and the '30 day lookback date' value. (columns B&C)

thank you for any input!

Steve

Count.PNG

Accepted answers

markcurry

Should you have another date file, for checking if it's between Trade Date and '30 day lookback date' , because the Trade Date will always be in that range.

You can do this with the Multi-Row tool, but it might be easier with 'Running Total' tool. To make your formulas easier to follow, you could firstly add a field to determine if the date is within the range, or the create a new Sum Buys field and just take the value if it is in the range.

If you group by the VLL ID, both the Multi-Row tool and Running Total tool will resort your data, if you don't want it to resort your data, you can add a more complicated formula to your multi-row tool, to check IF [Row-1:VLL Id] != [VLL Id] AND [Date In Range] THEN ...

See the attached workflow with the 3 options, there may be a better way, but hope that helps.

Multi-Row Example.yxmd

All comments

markcurry

Should you have another date file, for checking if it's between Trade Date and '30 day lookback date' , because the Trade Date will always be in that range.

See the attached workflow with the 3 options, there may be a better way, but hope that helps.

Multi-Row Example.yxmd

RolandSchubert

Hi @stevecassidy7777 ,

I think, it's not really a Multi-Row Formula problem, although it would be possible solving it using a Multi-Row Formula. I would generate a list of dates in the period for ech date using the Generate Rows tool, join this dates to the data and use a Summarize tool to calculate totals for each day. I've attached a sample workflow, including a sample using the Multi-Row Formula tool. What do you think?

Best,

Roland

SumTransactions.yxzp

stevecassidy7777

Thanks for the response. This is along the lines of what we were looking for!

stevecassidy7777

Hello Roland- thanks so much for the response. Unfortunately, I wasn't able to open the workflow because it was created in a newer version of Alteryx but I was able to get a solution from the above response.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335