How to exclude earliest dates from date range

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.

Date Time

Accepted answers

estherb47

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

FilterOutEarliestDate.yxmd

All comments

PeterA1

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.

smoskowitz

I'm wondering if the Generate rows tool would help you. Have you tried investigating that one?

BenMoss

I'm not too sure why you cant use the sample tool here? It has the group by functionality which would allow you to skip the first row per investment.

Or perhaps it could even be simpler by just using a filter tool to exclude instances where the start value is empty.

But perhaps I'm not understanding the data structure correctly.

Ben

AllenG

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.

estherb47

Hi @AllenG

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

FilterOutEarliestDate.yxmd

AllenG

This works! Thank you for the time you spend to figure this out and post.

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703