topic Hierarchy calendar as filter box in QlikView

Hierarchy calendar as filter box

Fri, 22 Nov 2013 08:01:47 GMT

Hi,

I try to implement a List box as tree that should look like the following:

Idea is that you shall be able to either select a fiscal year or go down to quarters and months and select all relevant data that occurs in this period of time.

I tried to adapt this example from Alexander: http://community.qlik.com/thread/5016

I've attached my sample application with only few test data.

Can someone pls help me? Maybe there is even a better / easier solution 🙂

Thanks

Re: Hierarchy calendar as filter box

bbi_mba_76 — Fri, 22 Nov 2013 10:02:45 GMT

Hi,

could you try with this ?

Kalender_Parent_Child:

LOAD DISTINCT right('0'&Month(OLI.SERVICE_DATE),2)&'-'&Year(OLI.SERVICE_DATE) as Child,

'Q'&ceil(Month(OLI.SERVICE_DATE)/3)&'-'&Year(OLI.SERVICE_DATE) as Parent

RESIDENT Kalender;

LOAD DISTINCT 'Q'&ceil(Month(OLI.SERVICE_DATE)/3)&'-'&Year(OLI.SERVICE_DATE) as Child,

Year(OLI.SERVICE_DATE) as Parent

RESIDENT Kalender;

LOAD DISTINCT num#(Year(OLI.SERVICE_DATE)) as Child,

'-' as Parent

RESIDENT Kalender;

LOAD OLI.SERVICE_DATE,

OLI.SERVICE_DATE as Child,

right('0'&Monat_Num,2)&'-'&Geschäftsjahr as Parent

RESIDENT Kalender;

LOAD DISTINCT right('0'&Monat_Num,2)&'-'&Geschäftsjahr as Child,

'Q'&ceil(Monat_Num/3)&'-'&Geschäftsjahr as Parent

RESIDENT Kalender;

LOAD DISTINCT 'Q'&ceil(Monat_Num/3)&'-'&Geschäftsjahr as Child,

Geschäftsjahr as Parent

RESIDENT Kalender;

LOAD DISTINCT num#(Geschäftsjahr) as Child,

'-' as Parent

RESIDENT Kalender;

H_Date:

HIERARCHY(OLI.SERVICE_DATE, v_Node, Node, , Node, Path)

LOAD Child as OLI.SERVICE_DATE,

Child as Node,

Parent as v_Node

resident Kalender_Parent_Child;

Re: Hierarchy calendar as filter box

sathishkumar_go — Fri, 22 Nov 2013 10:31:46 GMT

HI, I think you no need to use the "HIERARCHY" function. create a composite fields like, num#(Geschäftsjahr) & '^' & 'Q'&ceil(Monat_Num/3)&'-'&Geschäftsjahr & '^' & Monat_Num&'-'&Geschäftsjahr & '^' & OLI.SERVICE_DATE as HierarchyLevel in calendar table and In front end, In listbox (HierarLevel), use tree view "^". It will the hierarchy level. Hope this will helpful for u. Regards Sathish

Re: Hierarchy calendar as filter box

Fri, 22 Nov 2013 10:45:11 GMT

This leads to the following structure that looks a bit strange:

The bigger problem is, that clicking on any node leads to a complete deselection of all data.

Re: Hierarchy calendar as filter box

Fri, 22 Nov 2013 10:50:27 GMT

The problem you meet is due to the fact that the upper nodes are NOT related to data.

You must create a table that will do the bride betwwen upper nodes and lower nodes that have data.

I had created a document explainig that:

http://community.qlik.com/docs/DOC-4823

It was not for ime, but it should not change anything for you

Fabrice

Re: Hierarchy calendar as filter box

Fri, 22 Nov 2013 10:51:41 GMT

I've created this new hierarchy level, but the result is the same. The structure looks good, but I still cannot click on either years or quarters.

Re: Re: Hierarchy calendar as filter box

Sat, 04 Jan 2014 20:25:01 GMT

Please find the attached the qvw for reference. We need create the data on each node level for selection.