Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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.

1 Solution

Accepted Solutions
Not applicable

Re: Date format with drill down

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

12 Replies
pokassov
Valued Contributor

Re: Date format with drill down

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
Valued Contributor III

Re: Date format with drill down

PFA. Hope it will help you.

Re: Date format with drill down

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

Not applicable

Re: Date format with drill down

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.

Not applicable

Re: Date format with drill down

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!

Not applicable

Re: Date format with drill down

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
Valued Contributor III

Re: Date format with drill down

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.

Not applicable

Re: Date format with drill down

Hi,

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

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

Re: Date format with drill down

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

Community Browser