Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Karahs
Partner
Partner

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
Partner
Partner

How many years do you have?

Besides 1980??

Karahs
Partner
Partner
Author

1980-2017

OmarBenSalem
Partner
Partner

You have 28 Excel table with that format??

OmarBenSalem
Partner
Partner

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?

Karahs
Partner
Partner
Author

All year months and quarter are in rows

OmarBenSalem
Partner
Partner

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

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

martinpohl
Partner
Partner

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

Karahs
Partner
Partner
Author

I have attached the document.