Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi-Row Formula

cpowers
7 - Meteor

I have a set of Pricing Data, see attached for sample. Where I have a record for every price per the effective date. I want to know how long since the most recent effective date an asset has been that price. Which I accomplished using Multi-row formula. For example the price on 4/29/22 was 99.718 and it has been that price since 3/30/22. I want to be able to ignore all the records before 3/30/22 and do a summary like this (for all assets - this is just 1). I am not sure if I need another multi-row formula to indicate a Y/N of 'most recent price indicator' so I can ignore the rest and get my min/max date or if some other tool(s) would be better. I will be working with millions of rows so efficient processing would be preferred. Thanks in advance for any help/advice!

 

Desired Summary for Asset ID/Current Price

PRIMARY_ASSET_IDSECURITY_PRICE_AMTDays at PriceEFFECTIVE_DTFirst_DT_of_Current _price
123499.718214/29/20223/30/2022
5 REPLIES 5
IraWatt
17 - Castor
17 - Castor

Hey @cpowers,

From what I understand of the question you could just use a summarize tool and group on the Asset ID:

IraWatt_0-1659638284349.png

That should get you the details you want from each.

 

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

 

cpowers
7 - Meteor

@IraWatt thanks for responding. I should have been more clear in my post that although I've included a column in the sample data of "most recent Price" = Y/N - that is actually what I am struggling to create so that I can do the desired summary I included in my post/that you provided in your response. 

IraWatt
17 - Castor
17 - Castor

Ah okay I have added that formula in:

IraWatt_0-1659639132114.png

I also Grouped by Asset ID so that it should apply separately to each ID. Let me know if it makes sense/works ðŸ˜… @cpowers 

cpowers
7 - Meteor

@IraWatt YYESSS, thanks you. I'm the worse at Multi-Row. I went back to my original data and the grouping on asset is key for the solution to work. Thanks again. I've accepted you response as the solution. 

IraWatt
17 - Castor
17 - Castor

No worries @cpowers ðŸ˜† great to hear it worked! 

Labels
Top Solution Authors