I have a table with a date field with the format YYYYMMDD e.g., 20140403.
However, I want the dates to become D-M-YYYY (03-04-2014) and an extra field 'MonthYear' displaying the mmm-YYYY e.g., april-2014.
I already received some feedback and got the following solution:
Date(Date#([Trade date],'YYYYMMDD'),'D-M-YYYY') as [Trade date],
month(Date#([Trade date],'YYYYMMDD'))&'-'&year(Date#([Trade date],'YYYYMMDD')) as [MonthYear],
However, I want the possibility to perform a drill down so that when I click on a bar in a graph that shows april-2014 the underlying dates show up. I thought of a left join with a table with all the dates and the month year, but this needs to be done automatically in 2015 and for the following years as well.
It worked! I used the script below to replace the field [Trade date] which was in my case the field containing the dates. For some reason it did not work properly, but it has to with the data I used. Thanks for the help everyone!
Date(Date#([Trade date],'YYYYMMDD'),'D-M-YYYY') as [Trade date],
month(Date#([Trade date],'YYYYMMDD'))&'-'&year(Date#([Trade date],'YYYYMMDD')) as [MonthYear]
Thanks for the reply. That's what I thought initially as well, however, they are all sorted correctly. But I replaced my script with yours and the outcome is the same. I will use yours when I encounter the problem with another data set.
