topic Re: How to exclude earliest dates from date range in Alteryx Designer Desktop Discussions

How to exclude earliest dates from date range

AllenG — Fri, 26 Jul 2019 18:56:33 GMT

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.

Re: How to exclude earliest dates from date range

PeterA1 — Fri, 26 Jul 2019 19:24:43 GMT

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.

Re: How to exclude earliest dates from date range

smoskowitz — Fri, 26 Jul 2019 20:36:12 GMT

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

Re: How to exclude earliest dates from date range

BenMoss — Sat, 27 Jul 2019 08:03:10 GMT

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

Re: How to exclude earliest dates from date range

AllenG — Tue, 30 Jul 2019 17:29:59 GMT

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.

Re: How to exclude earliest dates from date range

estherb47 — Tue, 30 Jul 2019 18:19:21 GMT

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

Re: How to exclude earliest dates from date range

AllenG — Tue, 30 Jul 2019 19:19:48 GMT

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.

Re: How to exclude earliest dates from date range

NickSm — Wed, 31 Jul 2019 13:49:03 GMT

@AllenG

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.

Re: How to exclude earliest dates from date range

estherb47 — Thu, 01 Aug 2019 15:18:32 GMT

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

Re: How to exclude earliest dates from date range

AllenG — Fri, 02 Aug 2019 18:26:31 GMT

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