topic Re: Year Month Day Hiearchy in QlikView

Year Month Day Hiearchy

Wed, 05 Mar 2014 13:41:00 GMT

Hi,

If I have a datefield which datetype is YYYY-MM-DD.

And i have These fields:

Year(DateField) as Year

Month(DateField) as Month

Day(DateField) as Day

How can i then make a hiearchy in a listbox ? Where i can select all attributes?

Re: Year Month Day Hiearchy

Wed, 05 Mar 2014 13:54:18 GMT

Hi Thomas,

two ideas for this:

a) create an expression within listbox (instead of one single field):

=Year & '/' & Month & '/' & Tag

and check "View in treeview"

b) create a according Drill Down Group with Year, Month, .... (see document propers --> group Tab ...)

After creation use it in the listbox

HtH

Roland

Re: Year Month Day Hiearchy

Wed, 05 Mar 2014 13:54:24 GMT

Create a string that will look like this as an example

DateHierarchy

2014|January|01

2014|January|02

2014|January|03

2014|January|04

2014|January|05

2014|February|10

2014|February|11

and in your list box click the 'show as tree view' with pipe as your delimiter, '|'

to create the string simply do Year(DateField)&'|'&Month(DateField)&'|'&Day(DateField)

Cheers,

Byron

Re: Year Month Day Hiearchy

Wed, 05 Mar 2014 14:03:48 GMT

If i do like this i cannot select the top level. I can only select the lowest level Day

Re: Year Month Day Hiearchy

Wed, 05 Mar 2014 14:05:29 GMT

I can only select the lowest level Day

Re: Re: Year Month Day Hiearchy

Wed, 05 Mar 2014 14:37:34 GMT

Hi Thomas,

The trick is to create a field that contains the level at each level with a added delimiter. In the attached qvw I wanted to show you what your field needs to look like to achieve the correct hierarchy. You have to take advantage of the peek function in this case.

For now I have used some rudimentary if statements to achieve what you wanted. let me know if you come right man

Re: Year Month Day Hiearchy

Wed, 05 Mar 2014 14:44:39 GMT

Thomas,

You can do something quite simple:

Time2:
Noconcatenate
LOAD Date, Year & '|' & MonthName(Date) & '|' & Date as Path
Resident Time;
LOAD Date, Year & '|' & MonthName(Date) as Path
Resident Time;
LOAD Date, Year as Path
Resident Time;

Where Time is a first table with some dates, months and year fields, as you have.

By creating a second table, you will multiply the lines between Path (pls rename it) and the dates. So that you can choose a year and get data in your chart.

In the listbox, choose the Path field, and choose the delimiter (here the pipe) to get a treeview

Fabrice