Date format with drill down

[#1]

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.

[pokassov]

Hi!

You can add table Calendar using this reference and link your "data" field in fact table with calendar.

The Fastest Dynamic Calendar Script (Ever)

[senpradip007]

PFA. Hope it will help you.

[avinashelite]

Hi Lans,

Using the formula what you have, create a drill down group in Qlikview. Then include this as dimension the Bar chart it will exactly act as you required.

To create the Groups> go to documents  setting>Groups > new group > and select Drill down and include all your fields in the order

[#5]

Hi,

Thank you for the reply. However, I need to write instructions for this and even though it looks very handy, I don't understand the script. Therefore, i cannot use it, but thanks for the help.

[#6]

Hi,

I have a personal edition and am out of possibilities to open qlikview documents. Can you paste the script directly in your post? Thanks!

[#7]

Hi Avinash,

Thanks for your reply. I already created a drill down group with MonthYear and date, but it only shows the dates and not the MonthYear. They are not connected for some reason.

[senpradip007]

Here is the script.

Tab1:

LOAD

  RowNo()*rand()*100 as Data,

  Date($(vMinDate) + RecNo() - 1) AS Date,

  Year($(vMinDate) + RecNo() - 1) AS Year,

  Month($(vMinDate) + RecNo() - 1) AS Month,

  Month($(vMinDate) + RecNo() - 1)&'-'&Year($(vMinDate) + RecNo() - 1) AS MonthYear,

  'Q'&ceil(Month($(vMinDate) + RecNo() - 1)/3) AS Quarter,

AutoGenerate vMaxDate - vMinDate + 1;

After that follow as Avinash R said.

[#9]

Hi,

For MonthYear use like below & try with adding in drill-down group,

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

[avinashelite]

Please share your app....Will work and share you the script here