Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Drill down by hierarchy Year then Month when data is in monthname() format?

Hi, my data set is structured by a master calendar with monthname() as the resulting value (Jan 2017, Feb 2017, etc). When using the filter pane I get a long list of months/years, but I would like to build a drill-down feature where the user can select the 1. year, and then the 2. month. Is this possible to do? Thank you for your help!

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: Drill down by hierarchy Year then Month when data is in monthname() format?

See the attached file:

7 Replies
OmarBenSalem
Esteemed Contributor

Re: Drill down by hierarchy Year then Month when data is in monthname() format?

Yes, It's perfectmy possible.

1) You create an hiearchical dimension as a master dimension composed of year and month:

Capture.PNG

2) You use a bar chart for example, and as a dimension, you choose the newly created one (the hierarchical):

Capture.PNG

Result:

Capture.PNG

To go the months, select a year and confirm, its months will appears as dimensions:

Capture.PNG

To go back to Year dimension, just click on the year (under the x-axis)

Hope this helps

Not applicable

Re: Drill down by hierarchy Year then Month when data is in monthname() format?

‌thank you Omar. My data isnt broken up by two columns (month, and year) the two dimensions required for your suggestion. Right now it's just 1 column/ dimension with month-year for example (Jan 2017). What are your thoughts on what to do next?

OmarBenSalem
Esteemed Contributor

Re: Drill down by hierarchy Year then Month when data is in monthname() format?

Can you provide me with a sample qvf application to directly work on?

Thanks !

Not applicable

Re: Drill down by hierarchy Year then Month when data is in monthname() format?

Hi Omar,

Attached is the qvf, the CalMonth dimension is the one I would be working with.

Thank you!

OmarBenSalem
Esteemed Contributor

Re: Drill down by hierarchy Year then Month when data is in monthname() format?

See the attached file:

Not applicable

Re: Drill down by hierarchy Year then Month when data is in monthname() format?

Thank you, subfield() did the trick!

OmarBenSalem
Esteemed Contributor

Re: Drill down by hierarchy Year then Month when data is in monthname() format?

What I would do Monica, is creating those 2 new fields in the sript:

suppose your table is as follow:

Table:

load*,

subfield(CalMonth,' ',1) as Month,

subfield(CalMonth,' ',2) as Year;


load

A,

B,

Churn,

...

,

CalMonth ,

...


from source;


And the use these 2 new fields !


Have Nice days Monic !