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

determine if either of two dates cover the last 12 months

Dakotas
6 - Meteoroid

Hi there,

 

I'm working on some client data and I'm trying to decide if a job was ever 'active' during the last 12 months.

 

I have start and end date fields for all of my jobs. For example, Job 2 started more than 12 months ago and will end in the future, but it falls into my target data set of jobs that have had active work in the last twelve months.

 

Any ideas on how I could get to determine this?

 

Thanks!

Dakota

 

JobStart dateEnd date
17/1/20182/1/2019
210/1/20165/1/2020
35/1/20188/1/2019
6 REPLIES 6
bpatel
Alteryx
Alteryx

hi @Dakotas,

 

I mocked up a workflow from what i think you are looking for. Let me know if this helps or is on the right track.

 

Dakotas
6 - Meteoroid

Thanks for taking a look! I think this helps to determine how long a job is but I don't think this will work in this instance as it doesn't capture the fact that Job 2 was active during the past twelve months. (started in 2016 but goes until 2020)

JosephSerpis
17 - Castor
17 - Castor

Hi @Dakotas I mocked up a workflow let me know what you think?

estherb47
15 - Aurora
15 - Aurora

Hi @Dakotas 

 

This approach generates all of the dates between the start and end date. If any fall within the last year (based on today's date), they will be assigned a value of 1, else 0. Add up all of those values for each row, and then filter where there are one or more date range matches.

 

image.png

 

Let me know if this helps. Instead of 2 DateTime parse tools, I used one MultiField formula. Either approach works very well.

 

Cheers!

Esther

Dakotas
6 - Meteoroid

Thanks! This worked well, I ended up switching to 365 day instead of 12 months to catch jobs that are less than a month long and the logic held there too 🙂

Dakotas
6 - Meteoroid

Thanks Esther, I went with an earlier solution to reduce the amount of data generated but I appreciate the insight, this would have worked well too 🙂

Labels