Need assistance with a Multi-Row formula configuration

[stevecassidy7777]

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 B) falls between the 'Trade Date' value and the '30 day lookback date' value. (columns B&C)

thank you for any input!

Steve 

[markcurry · Accepted solution]

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.

[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 

[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.