Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date format with drill down

Hi,

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.

12 Replies
Not applicable
Author

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]

FROM

jagan
Luminary Alumni
Luminary Alumni

Hi,

Using this approach you will get issues in sorting in charts, so try below script

=Date(MonthEnd(Date#([Trade date],'YYYYMMDD')), 'MMM-YYYY')  as [MonthYear]


Hope it helps you.


Regards,

Jagan.

Not applicable
Author

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.