topic Re: Date format with drill down in QlikView

Date format with drill down

Thu, 09 Apr 2015 06:47:42 GMT

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.

Re: Date format with drill down

pokassov — Thu, 09 Apr 2015 07:01:07 GMT

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)

Re: Date format with drill down

senpradip007 — Thu, 09 Apr 2015 07:07:32 GMT

PFA. Hope it will help you.

Re: Date format with drill down

avinashelite — Thu, 09 Apr 2015 07:14:19 GMT

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

Re: Date format with drill down

Thu, 09 Apr 2015 07:15:15 GMT

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.

Re: Date format with drill down

Thu, 09 Apr 2015 07:16:22 GMT

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!

Re: Date format with drill down

Thu, 09 Apr 2015 07:18:39 GMT

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.

Re: Date format with drill down

senpradip007 — Thu, 09 Apr 2015 07:23:27 GMT

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.

Re: Date format with drill down

Thu, 09 Apr 2015 07:30:13 GMT

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

avinashelite — Thu, 09 Apr 2015 07:30:14 GMT

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

Re: Date format with drill down

Thu, 09 Apr 2015 08:03:06 GMT

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

Re: Date format with drill down

jagan — Thu, 09 Apr 2015 08:21:49 GMT

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.

Re: Date format with drill down

Thu, 09 Apr 2015 08:32:47 GMT

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.