Multi-Row Formula
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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_ID | SECURITY_PRICE_AMT | Days at Price | EFFECTIVE_DT | First_DT_of_Current _price |
1234 | 99.718 | 21 | 4/29/2022 | 3/30/2022 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @cpowers,
From what I understand of the question you could just use a summarize tool and group on the Asset ID:
That should get you the details you want from each.
Any questions or issues please ask
Ira Watt
Technical Consultant
Watt@Bulien.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Ah okay I have added that formula in:
I also Grouped by Asset ID so that it should apply separately to each ID. Let me know if it makes sense/works 😅 @cpowers
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
No worries @cpowers 😆 great to hear it worked!
![](/skins/images/72080B1993C0EC7A53569ADF25905C2F/responsive_peak/images/icon_anonymous_message.png)