Date formula help!!

Okay, to me, this is gonna be a toughy formula. In Excel, no problem. Alteryx - I hardly know where to begin. So, to set the stage, here's a snip:

So, for example, pretend this is client #1 and 2(separated by the long line in between), I am trying to get like, Server History: End Date of 7/15/2010 to subtract from Server History: Effective Date of 10/4/2012. As you can see here, some would be 4/17/2016 - 4/18/2016 = 1 day. I'm trying to filter out anything greater than 1-day.

As far as I've gotten is I know I need to sort ascending by like Client ID, Server History: Effective Date then Server History: End Date. Beyond that - let's just say UGH!

Data Investigation

Accepted answers

All comments

Pawel_Paleczny

Hello MaryCann,

You can use MultiRow Formula to check the difference between dates, if configure as attached you'll get column with days check

PS. For last row it should not work correctly

check.JPG

Emmanuel_G

@MaryCann

Find in attachement the way of doing that. I did a test with sample of your data.

Let me know if there is any issue and please do not hesitate to mark this answer as solution if it helped.

Date formula.yxmd

MaryCann

@Pawel_Paleczny and @Emmanuel_G - It looks like it's a combination of the two of yours for the answer... Running right now... Will let you know the result!

MaryCann

@Pawel_Paleczny and @Emmanuel_G ... Okay - it mostly worked. Don't know why it worked on most of the fields, but, check this out:

the 34 there, there are more days than 34 between 2/5/2019 - 1/14/2020... And, that 2009, there are less days between 2/26/2013 and 9/26/2013. No idea what is going on! I also have an idea on how to remove the date Difference number when it's the end of the client section... Something like, IF MAX ([Server History: End Date]) = [Server History: End Date] THEN [Difference] = N/A else [Difference]. Your thoughts?

kathleenmonks

Hi @MaryCann,

Hard to tell where the error is without seeing your workflow. But assuming you have other data that identifies a particular client from another - you can use the group by functionality in the multi-row tool on that ID. This will then calculate the final row for each client as Null and you can filter those out when you filter out the rows =1. I tested this with the attached workflow. I added all the data you've screenshot and got different calculations than what you showed (i.e. instead of 34 it is 343 and instead of 2009 it is 212.) If you're able to share more of your data/workflow, we can help troubleshoot why you're getting the above results.

Date Calc.yxmd

MaryCann

@kathleenmonks ... kinda new at this, hope the below makes sense. I have this as the flow:

The Auto Field, I have clicked Server History: Effective Date and Server History: End Date only. This is so the sort will work with the Client ID first, then Server History: Effective Date then Server History: End Date. The select button prior to DateTimeParse changes the Server History: Effective Date & Server History: End Date to V_String so the DateTimeParse formula will work. Here's that snip:

The Calculate the differences field looks like:

I removed the Difference filter, as I realized knowing 1-day turn around might be important. So, after I run this, I get:

UGH!

kathleenmonks

@MaryCann

In the tool where you are parsing the dates, you need to select the two fields in the top part of the configuration pane. You can also just convert them to Dates and not Datetimes. Also I would recommend changing the Client_ID to a string field so you can use it to Group By in the multi-row formula.

If the two things above don't fix it, can you show me a screenshot of the format and type of your date fields before they go through the process above?

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335