Trying to convert Month of the Year into Date with Magic ETL

hecl3

Basically the title. I am using a dataset that pulls Month of the Year instead of Date, as the User count with the Date metric isn't accurate. However I have run into issues trying to convert Month of the Year into a date format. Here is the original format:

As you can see, each month is numbered from 1-12. I used Value Mapper to convert the numbers to more of a date format, see here:

I thought from there I should be able to convert the integer strings to a Date. However I am having difficulties with that part. I'm not sure if I'm using the wrong formulas in the Formula tile. I tried DATE_FORMAT(Month of the year, '%M %d, %Y') but it didn't work in Magic ETL, though it did in Beast Mode. The Date Operations tile wasn't working for me either. Would really appreciate some guidance on this.

DashboardDude

@hecl3

Here's my video on how to do this using a CAST function:

https://www.youtube.com/watch?v=OPKuKCTRxH0

GrantSmith

Your format string isn't quite correct. You need something like

DATE_FORMAT(`Month of the year`, '%Y%m%d')

One concern though is that the months are set to a specific year - is this intended or will other years have the same month?

hecl3

Ok, I tried that and it looks like I'll need to convert it from an integer to a date first. This was the error message I got:

MarkSnodgrass

You can use the formula tile and use the concat and date functions to build your date and make it be recognized as a date field.

Here is an example where the date will always be in 2018

DATE(CONCAT(`Month of the year`,'/1/2018'))

Here is another example where it will extract the year from the current date

DATE(CONCAT(`Month of the year`,'/1/',YEAR(CURRENT_DATE())))

DashboardDude

@hecl3

Here's my video on how to do this using a CAST function:

https://www.youtube.com/watch?v=OPKuKCTRxH0

hecl3

Thanks everyone for your help! I was able to get it up and running