Alteryx designer Discussions

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

How do I return True values on records from 7th from the previous month?

Highlighted
Asteroid

Hi,

I need to build a formula looking into the 'Hire Date' column in my dataset and be able to return True value for records which are "hired in the last 30 days or future".

 

I want the condition to always be able to benchmark it against 7th of the current month.  As an example, I run the workflow on the 10th of June (current month).  I would like the "hired in the last 30 days or future".to return True value on records >= 7th of May (previous month). Basically, I would like the workflow to always return True values on records from 7th from the previous month. 

 

How do I do that?  Thank you in advance! 

Highlighted
Alteryx Partner

Hi @BJSOON ,

 

I have a query regarding your problem statement. As you mentioned that the current date is 10th of June and would be comparing with 7th of May, so my question is what if the Hire Date is 6th of June will it be compared with 7th June or the previous month that is 7th of April?

 

Thanks.

Highlighted
Asteroid

Hi, it will be compared with 7th May.  So with the correct formula applied, the person with hire date 6th June will have a 'True' value because my condition is "Hire Date in last 30 days or future?"

Highlighted
Castor
Castor

Hi @BJSOON 

 

Here's a workflow that returns what you're looking for

 

WF.png

 

All the work is done in the formula tool which looks like this

 

Formula.png

The first formula build the benchmark date by subtracting 1 month from the current date and then extracts the year and month parts of this and adds the "07" as the date.  The second part just compares the hire date with the benchmark.

 

Results

 

Results.png

 

To build the sample data set, I used 4 consecutive dates from May 06 to May 09 and ran the calculations for 3 current dates, May 10th, June 10th and July 10th.  The results for May are all true and for July are all False, as expected.  For June, the results switch from False to True when hire date reaches May 07.

 

 

Dan

Highlighted
Asteroid

Thank you so much!  This works! 

Labels