How to create hierarchy?

[Karahs]

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?

Thanks !

[OmarBenSalem]

How many years do you have?

Besides 1980??

[Karahs]

1980-2017

[OmarBenSalem]

You have 28 Excel table with that format??

[OmarBenSalem]

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]

All year months and quarter are in rows

[OmarBenSalem]

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

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

[martinpohl]

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]

I have attached the document.