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

Find a specific date

nogap2
7 - Meteor

Hi, I have a list of data that has calendar dates in it historically.  I'm looking to identify the start of the most recent Fiscal Year in the data and have run into a wall.  Fiscal year start if always November 1.  Is there a date function that will allow you to get the last day of the previous year and I can go back 2 months and add 1 day?  

 

Date 
1-Jan-17 
1-Feb-17 
1-Mar-17 
1-Apr-17 
1-May-17 
1-Jun-17 
1-Jul-17 
1-Aug-17 
1-Sep-17 
1-Oct-17 
1-Nov-17Find this date
1-Dec-17 
1-Jan-18 
1-Feb-18 
1-Mar-18 
1-Apr-18 
1-May-18 
1-Jun-18 
1-Jul-18 
1-Aug-18 
1-Sep-18 

 

6 REPLIES 6
Kenda
16 - Nebula
16 - Nebula

Hi @nogap2!

 

Because the date you're looking for is always the same date, could you just filter on that date without using date functions/calculations? By this, I mean just use a Filter that says 

contains([Date],"1-Nov")
tcroberts
12 - Quasar

I'm not sure exactly what the complications are here, but if I were to approach this problem, I'd probably try and extract out a year from the dates, which you can do with DateTimeFormat(DateTimeParse([Date], '%d-%b-%y'), '%Y%")

 

DateTimeParse turns the field into the Alteryx formatted Date, and DateTimeFormat selects out the 4 digit year (%Y), can get 2 digit year from (%y).

 

You could then do something like:

 

 

IF DateTimeParse([Date], '%d-%b-%y') = DateTimeParse([Year]+'-11-01', '%Y-%m-%d')

THEN 1

ELSE 0

ENDIF

to tag the rows which contain the 1st of November.

 

I there's some reason you can't use something like this please let me know,

 

Cheers!

 

EDIT: @Kenda's solution is much quicker and easier without unnecessary computations or saving of data.

 

 

 

nogap2
7 - Meteor

Thanks for the help!

 

It may be a little convoluted way of going about it, but  I was able to get it working through the DateTime functions. 

First I found the Fiscal Year End with:

 

IF [Month] >=11 THEN
DateTimeAdd(DateTimeAdd([Calendar Year Start],+22,"Months"),-1,"Days")
ELSE DateTimeAdd(DateTimeAdd(DateTimeAdd(DateTimeTrim([Date],"Year"), 1, "Year"), -2,"Months"),-1, "Day")
ENDIF

 

Then I went back one year less one day

I ended up needing both Fiscal Year end and Start in my formulas so it worked out.

DateTimeAdd(DateTimeAdd([Fiscal Year End],-1,"Year"),1,"Day")

neilgallen
12 - Quasar

@nogap2

 

A bit more direct formula would look like this:

 

datetimeadd(datetimeadd(datetimeadd(datetimeparse(datetimetoday(),"%Y"),-1,"days"),-2,"months"),1,"days")

 

The datetimeparse will give you the current year, defaulted to January 1. From there you're just subtracting and adding dates per your request above (subtract one day, go back two months, then add a day).

 

If you're always trying to get back to Nov 1, then you could be simpler with just

 

datetimeadd(datetimeparse(datetimetoday(),"%Y"),-2,"months").

 

 

nogap2
7 - Meteor

I tried the year start then back two month's, but if the date is in December, that wouldn't work as a Fiscal Year is from Nov1-Oct31.  December dates caused the wrong fiscal year.

I'll see if I can leverage this but through am If statement that will correct for November and December dates.

nogap2
7 - Meteor

I got it working!  way easier to read now and the November and December dates move forward to the next year:

 

FY Start (Nov 1) =

IF [Month] >=11 THEN datetimeadd(datetimeadd(datetimeparse([Date],"%Y"),-2,"months"),1,"Year")
ELSE datetimeadd(datetimeparse([Date],"%Y"),-2,"months")
ENDIF

 

FY End (Oct 31) =

IF [Month] >=11 THEN
datetimeadd(datetimeadd(datetimeadd(datetimeadd(datetimeparse([Date],"%Y"),-2,"months"),1,"year"),-1,"Days"),1,"Year")
ELSE  datetimeadd(datetimeadd(datetimeadd(datetimeparse([Date],"%Y"),-2,"months"),1,"year"),-1,"Days")
ENDIF

Labels