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.
kshitijas
Contributor

How to create hierarchy?

Hi Community,

My query is how can i create hierarchy  from the below data , as i have different rows with years,months and quarter. I need to make a drill down of year , month and quarter in a graph.

Any suggestion on how to create it into separate dimensions drill down?

Capture52.PNG

Thanks !

8 Replies
OmarBenSalem
Esteemed Contributor

Re: How to create hierarchy?

How many years do you have?

Besides 1980??

kshitijas
Contributor

Re: How to create hierarchy?

1980-2017

OmarBenSalem
Esteemed Contributor

Re: How to create hierarchy?

You have 28 Excel table with that format??

OmarBenSalem
Esteemed Contributor

Re: How to create hierarchy?

can you attach the 1980 , 1981 and 1982 excel files?

So that I can see how implement a generic way to transform all of your tables into the desired format and be thus able to create your hierarchy?

kshitijas
Contributor

Re: How to create hierarchy?

All year months and quarter are in rows

OmarBenSalem
Esteemed Contributor

Re: How to create hierarchy?

How so? Can you screen shot your data source with 2 years?

I'm trying to help; you're not facilitating it

martinpohl
Valued Contributor II

Re: How to create hierarchy?

Here is the script:

Temp:

crosstable (MonthYear,Value,2) load

*

from your data;

Data:

load

"Commodity Name",

Commodity Code",

subfield(MonthYear,'M',1) as Year,                    //seperate Year

subfield(MonthYear,'M',2) as Month,                    // seperate Month

'Q' & ceil(subfield(MonthYear,'M',2)/3) as Quarter,               //create Quarter

Value

resident Temp

where MonthYear like '*M*';

drop table Temp;

Maybe add other timefields you need.

Regards

kshitijas
Contributor

Re: How to create hierarchy?

I have attached the document.