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 ID | Segment | Category | Company | Ship Date | Difference |
CA-2017-122765 | Home Office | Furniture | A ltd. | 2017-03-28 | 0 |
CA-2017-122771 | Corporate | Furniture | A ltd. | 2017-07-02 | 96 |
CA-2017-122774 | Corporate | Furniture | A ltd. | 2017-10-02 | 92 |
CA-2017-122763 | Consumer | Office Supplies | B ltd. | 2017-03-20 | 0 |
CA-2017-122768 | Corporate | Office Supplies | B ltd. | 2017-05-25 | 66 |
CA-2017-122770 | Consumer | Office Supplies | B ltd. | 2017-06-15 | 21 |
CA-2017-122772 | Corporate | Office Supplies | B ltd. | 2017-08-11 | 57 |
CA-2017-122775 | Consumer | Office Supplies | B ltd. | 2017-10-05 | 55 |
CA-2017-122764 | Consumer | Technology | C ltd. | 2017-03-25 | 0 |
CA-2017-122766 | Consumer | Technology | C ltd. | 2017-04-23 | 29 |
CA-2017-122767 | Corporate | Technology | C ltd. | 2017-04-29 | 6 |
CA-2017-122769 | Consumer | Technology | C ltd. | 2017-05-26 | 27 |
CA-2017-122773 | Corporate | Technology | C ltd. | 2017-09-13 | 110 |
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.
@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: