Hi, I'm reconciling investment returns pulled from sql using Beginning Market Value and Ending Market Value. Our tables only contain Ending Market Values so for the Beginning Mkt Val, I'm using Multi-Row formula tool to create Beg Mkt Val from prior dates End Mkt Val. I'm also needing to run for a rolling 60 day period, but this conflicts with using the Multi-Row formula for the first dates Beg Mkt Val, since there is no prior End Mkt Value to use, so the first dates data for each investment is not valid. I can use the Fliter tool to remove the first date in date range, but this would need to be updated each day and I'm hoping to automate report. The number of investments we have changes frequently as well, so this precludes me from using Sample tool to skip X numbers of row (number of investments), so looking for formula/tool to exclude the earliest date in the rolling 60 day date range. Thanks.
Solved! Go to Solution.
Hi @AllenG Do you have an example of your workflow? I can take a look.
From what I understand you need to:
1) Have a formula take the previous day's market close (row-1) and input into a new column as the beginning of day value.
2) Have this roll for a 60 day period (e.g dropping off oldest row each time a new day is added)
Let me know if I have misunderstood any part of your desired output.
I'm wondering if the Generate rows tool would help you. Have you tried investigating that one?
Thanks, but it seems the sample tool only allows you to skip a certain number of of rows. Since the number of investments (rows) changes frequently, this number would have to updated each time and that doesn't make for good automation of reporting. Let me know if I'm missing something about the Select tool.
Hi @AllenG
Are you always looking to pull the 60 most recent lines in your dataset? You could always sort in reverse date order (highest on top), and use Sample to pull the first 60 records.
Let me know if that helps.
An example of your data (dummy data of course) would be helpful as we try to troubleshoot this for you.
Cheers!
Esther
No, I'm looking to exclude the earliest date for each investment and the number of investments changes frequently. So, for example if I'm I'm pulling 60 day's of returns for 100 investments and the earliest date is 6/1/2019, I want to exclude 6/1/2019. Some of suggested that I just sort by date and exclude first 100 rows, but since the number of investments frequently change, I'd have to keep monitoring and updating the workflow. I'm attaching output data if that helps.
By sorting by investment and date, then excluding the first entry per investment, would this accomplish what you're looking for? It becomes dynamic where the first date for each investment will be excluded, regardless of how many investments / records per investment there are.
Hi @AllenG
Thank you, now I understand why the Sample tool on its own won't work. But, you can use it (with sort) to pull the earliest date out of your data, append the first date back into your data set, and then filter on what doesn't match that first date.
This is dynamic and doesn't require monitoring. It will always exclude matches to the first date.
Let me know if this helps.
Cheers!
Esther
This works! Thank you for the time you spend to figure this out and post.