How to convert the string MM, YYYY to datetime format MM YYYY?

Question

Hi,

I have been working with a dataset of daily sales orders. I have found a way to aggregate those daily orders into monthly figures (i have done in 2 steps - both using Summarize functions: first grouping multiple orders each day into daily totals by grouping by OrderDate and then applying the same approach to aggregate daily totals and grouping them by month_year).

Problem is that to get to the 2nd Summarize, I had to convert datetime format of YYYY-mm-dd (e.g. 2015-01-31) into string format of MM, YYYY (e.g. January, 2015).

Now, to do time series forecasting, I need to convert this MM, YYYY strings back into datetime dtype.

I'm struggling to this since when I try to use DateTime Parse function, it converts back to YYYY-mm-dd (e.g. 2016-03-20)

I need to get rid of days in datetime, and only to keep months and years (e.g. February 2021).

So 2 ways I hope you can help with are:

1. how to convert string in the format MM, YYYY (January, 2015) into datetime format MM YYYY (January 2015)?

or

2. how convert datetime format YYYY-mm-dd (2015-01-31) into datetime with only months and years MM YYYY (January 2015) or mm/YYYY (01/2015)?

i have tried to do this with function DateTimeFormat(DateTimeParse([Month_Year], '%B,%y'), '%B-%Y') - I'm getting

"ConvError: Formula (56): MonthOfYear: January-20 is not a valid Date"

I have also tried conversion with DateTime Parse from string to datetime but I keep getting days back (so YYYY-mm-dd - e.g. 2020-01-26).

Why Alteryx doesn't expand Datetime Parse function to convert from one datetime format to another datetime format?

Please help,

Thx.

Alteryx 3.png

Alteryx 1.png

Alteryx 4.png

Alteryx 2.png

ArtApa · Answer

Hi @GalusAnonim - If you want to build a Time Series forecast you don't have to have a field in Date or DateTime format. I suggest you to look at the following resources:

1) Time Series Forecasting Interactive Lessons: https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Time%20Series%20Forecasting

2) Cashflow Forecasting workflow from the Starter Kit: https://www.alteryx.com/starter-kit/financial-analytics

In the workflow above the field Name is in the String format. However the format is YYYYMM, not MMYYYY. This is to allow you to sort your data and prepare it for a Time Series building block.

Let me know if I can assist any further.

ImadZidan · Answer

Hello @GalusAnonim ,

You have rolled-up the summary to a monthly level . So for each group and month you have one record. I don't think it matters for the forecasting as you will set set your level to monthly. You can set the days to be the first day of the month or the last day of the month. If you are trying to forecast sales or order for the next months, then that shouldn't be a problem.

Now to answer you query, You can not convert string to date without getting the day. This is Altyrex way of dealing with dates. This discussion is popular one and one day Alteryx will perhaps make that option available.

If you have a sample file, We can build the forecast.

I hope this is helpful.

AbhilashR · Answer

Hi @GalusAnonim, would it help if we use the DateTimeTrim function within a formula tool to normalize your dates for a given month? It is a completely different approach to what you have, but doing so helps trim the day element of a date to the 1st of the month, which in turn helps summarize the data for a given month/year combination (i.e. 2021-01-14 becomes 2021-01-01). This approach also helps retain the Date datatype and might help with time series analysis. Something like the logic in the screenshot below: