12 Replies Latest reply: Apr 9, 2015 4:32 AM by Lance Chong RSS

    Date format with drill down



      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.