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

Simple - multi row formula and date conversion

Kirstyp
8 - Asteroid

Hi

 

I have this great workflow that I was helped with by the community - it basically uses dates of data to identify new admissions into residential care.

 

I have changed my data source and in the example attached the first multi row formula (to generate Fiscal year) is not picking up the Long Term residential care placement and I don't know why? Please help.

 

Also, if I want to change the fiscal month formula from calculating a number for month (i.e. April is 4) then how do I do this? I want to use in tableau with a date field rather than a number, but still keep the format of the year and month grouping. 

 

In the attached the client should be identifying 1 new admission to care. 

 

I know some of the summarises aren't working - this is just due to removing some of the grouping from my data source.

7 REPLIES 7
NicoleJohnson
ACE Emeritus
ACE Emeritus

Hi @Kirstyp!

 

Think I see the issue - in your text input tool that you're starting with, looks like you hard-typed "NULL" in to the spaces that don't have info, instead of just leaving them empty. Alteryx will look at these as the word "NULL" rather than the absence of any data Null(), so when you have IsNull() in your formulas to determine dates, it's not recognizing that they're supposed to be empty. If you remove the NULL words in your text input, I believe the date formulas will work better.

 

NOTE: I tried this out, and noticed that the formula for FiscalYearStart results in a -1 for the date since there isn't a StartDate for a couple of your records... so you'll likely want to add in some logic for what result you want to see if the StartDate is null. :)

 

Cheers,

NJ

Kirstyp
8 - Asteroid

Perfect, thanks such a easy spot.  Sorry , can you help again please, the formula to identify 'new' isn't working.  So it should be picking up this long term placement is a new long term (as there wasn't any other long term placement with a consecutive date), I think the formula is looking for transfer from short to long - which is also correct, so it should be new if no placement before it (which is not long) or transfer from short to long.

 

Just need to add a filter to filter out the null start dates.

 

Sorry Nicole, I think you originally helped me with this so I'm being a pest!

TerryT
Alteryx Alumni (Retired)

I think I agree with @NicoleJohnson that there is a growing problem starting at Formula (11) where FiscalYearStart is computed to be -1.  The Generate Rows (12) tool then starts from -1 and counts to FiscalYearEnd and the data grows from 5 rows to 4041 rows.  (Then Generate Rows (37) for Months for every year in that range, the data is multiplied by 12 months to 48440 rows -- this just doesn't seem like what you want).

 

I've corrected and reworked the Formula (11) tool with a Formula / Select combination to create (and delete) temporary variables to make the logic a bit more maintainable:

 

IF [effective_start_date] < [current_fiscal_year_boundary]
THEN [last_fiscal_year]
ELSE [this_fiscal_year]
ENDIF

 

Hope this helps!

 

 

Terry T

TerryT
Alteryx Alumni (Retired)

Oh, I also meant to respond to the question:

 

Kirstyp:  I want to change the fiscal month formula from calculating a number for month (i.e. April is 4) then how do I do this?
 
 

 

The reference page:

http://downloads.alteryx.com/betawh_xnext/index.htm#Reference/DateTimeFunctions.htm#Format 

shows date specifiers.

 

I think you want the %B specifier (for the Full Month Name).

Although I didn't do it in the sample snippet I shared before, the [current_fiscal_year_boundary] could be specified as follows:

 

DateTimeParse("2017-April-01","%Y-%B-%d")

 

(I replaced the left(DateTimeToday(),4) part with "2017" since I think it makes the example more clear.)

 

Again, I hope this helps! 

 

Terry T

 

Kirstyp
8 - Asteroid

Hi

 

This is helpful, thanks , although applying the -1 for the fiscalyearend works in this scenario however I am applying this formula to a larger data set , dating back to 2012.

 

Also, some of the original multi-row tools in the formula aren't calculating correctly now - 'the init formula resulted in a string but the field is numeric', I would like to show a row for each year and then a row for each month active,so I can identify all in care per month/year.

 

 

TerryT
Alteryx Alumni (Retired)

Very good point.  To be compatible I should have specified the Data type and used ToNumber() on the result:

Formula-Fix.PNG

 

TerryT
Alteryx Alumni (Retired)

Thanks @Kirstyp,

 

That gave me my "First Accepted Solution Badge" on Alteryx Community!  I know I only helped with a small portion and that your workflow does so much more.  Please let me know if I can help any more.  (I feel invested now)  :)

 

Thanks again!

 

Terry T

Labels