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.

Multi-row formula

Mj9715
8 - Asteroid

Hi all, I have a data set with column "Ship Date" which is in "%Y-%m-%d" format. I need to perform the following using a multi-row formula

 

1. Convert column to date format : ToDate([Ship Date])

2. Convert Ship Date to desired format : DateTimeFormat([Company], "%m/%d/%Y") 

3. Find difference in date between Ship date and current date grouped by company : DateTimeDiff(DateTimeToday(), [Ship Date], "days") 

 

The output column is shown as difference. Can someone please suggest where my formulas are going wrong? I am getting error with converting the "Ship Date" using the formula. Appreciate if someone can tell me how to wrap these three formulas in one line to avoid multiple tiles - preferabbly using "Multi-row formula" tool. 

 

Input (all columns up to ship date) & output is Difference 

Order IDSegmentCategoryCompanyShip DateDifference
CA-2017-122765Home OfficeFurnitureA ltd.2017-03-280
CA-2017-122771CorporateFurnitureA ltd.2017-07-0296
CA-2017-122774CorporateFurnitureA ltd.2017-10-0292
CA-2017-122763ConsumerOffice SuppliesB ltd.2017-03-200
CA-2017-122768CorporateOffice SuppliesB ltd.2017-05-2566
CA-2017-122770ConsumerOffice SuppliesB ltd.2017-06-1521
CA-2017-122772CorporateOffice SuppliesB ltd.2017-08-1157
CA-2017-122775ConsumerOffice SuppliesB ltd.2017-10-0555
CA-2017-122764ConsumerTechnologyC ltd.2017-03-250
CA-2017-122766ConsumerTechnologyC ltd.2017-04-2329
CA-2017-122767CorporateTechnologyC ltd.2017-04-296
CA-2017-122769ConsumerTechnologyC ltd.2017-05-2627
CA-2017-122773CorporateTechnologyC ltd.2017-09-13110

 

2 REPLIES 2
Emmanuel_G
13 - Pulsar

@Mj9715 

 

Step 2 : You applied DateTimeFormat to Company field.

 

Another thing I could advise you is to reverse steps 2 and 3. Because when you apply a DateTimeFormat to a date, it becomes of type string. And with in DateTimeDiff, both fields must be dates. Which is not the case if we apply the DateTimeformat before making the difference between the dates.

 

Finally, in my opinion, no need to apply the DateTimeformat in the multi-row formula. It is better to do it in a Multi-field or formula tool.

DataNath
17 - Castor

@Mj9715 how does this look? You'll need to create a new field for the date format you're after as your date is already in standard date form and you need to change it to a string and use the datetimeformat() function for the MM/DD/YYYY you're after. You also used [Company] in your date calc which would have definitely thrown it off. You can add more than one expression in the formula tool and so I'd just handle them both in one and then use the select to remove your old date field that isn't formatted properly:

 

DataNath_0-1663748129963.png

Labels