cancel
Showing results for 
Search instead for 
Did you mean: 
EXTENDED DEADLINE | Share your story at Inspire 2018! Accepting Call for Speakers Now - Submit by January 26th to qualify.

Simple - multi row formula and date conversion

SOLVED
Highlighted
Kirstyp
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.

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
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!

Alteryx
Alteryx

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

Alteryx
Alteryx

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

 

 

Alteryx
Alteryx

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

Formula-Fix.PNG

 

Alteryx
Alteryx

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