Simple - multi row formula and date conversion

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.

example-DC.xlsx

Admissions - DC.yxmd

Date Time

Accepted answers

TerryT

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

All comments

NicoleJohnson

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,

Kirstyp

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703