Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

How to exclude earliest dates from date range

AllenG
5 - Atom

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. 

9 REPLIES 9
PeterA1
Alteryx
Alteryx

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
12 - Quasar

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

BenMoss
ACE Emeritus
ACE Emeritus
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
5 - Atom

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
15 - Aurora
15 - Aurora

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

AllenG
5 - Atom

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. 

NickSm
Alteryx
Alteryx

@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.

 

clipboard_image_1.png

estherb47
15 - Aurora
15 - Aurora

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.

 

image.png

 

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

AllenG
5 - Atom

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

Labels