Hierarchy

[sagarkharpude]

Hi All,

I want to create hierarchy for the below data:

Item Category	Cost
Z002	195,439
Z003	-
Z003	21,025
Z003	42,049
Z003	38,286
Z003	21,025
Z003	32,445
Z003	-
Z003	24,529
Z003	16,080

Z002 is the sum of all Z003 categories. So by default it will show the sum of Z002. But when i expand the Z002 it will give me the Z003 category wise Cost. Output should be like below:

Default chart:

Item Category   Cost

Z002                195,439

When i expand the above Z002 Category output will be(Pivot table)

Item Category	Item Sub Category	Cost	Total
Z002	Z003	-	195,439
	Z003	21,025
	Z003	42,049
	Z003	38,286
	Z003	21,025
	Z003	32,445
	Z003	-
	Z003	24,529
	Z003	16,080

Thanks in Advance

[settu_periasamy]

Hi sagar,

How do you identify which one is sub-categroy and which one is Total? is there any logic?  Can you provide more sample with different category and sub-category?

[jagan]

Hi,

You have a maintain a hierarchy table like below

Data:

LOAD

*

INLINE [

Item Category, Item Sub Category

Z002, Z003

'

'

];

Now in chart (Pivot table) use Item Category, Item Sub Category  as dimensions and use Sum(Cost) as expression and enable Sub Totals in presentation tab.

Regards,

Jagan.

[sagarkharpude]

@Settu:

User gives the details about which one is Category and which one is sub category. For Category there is total of all the sub category.

[jagan]

Can you attach some sample data you are getting?  So that it would be easier to provide solution.

Regards,

jagan.

[sagarkharpude]

Hi,

Sample is attached for your info. PFA.

[jagan]

Hi,

In your sample data there is no flag which tells what it category and sub category.   What is the logic to find out that.

Regards,

Jagan.

[sagarkharpude]

User has told that Z002 is category and Z003 is sub category.

[avinashelite]

In  this case,  manually you need to create a hierarchy table or data relation and as per the user inputs you need to change the same.

Temp:

Hierarchy (Child, Parent, Child, ParentName, PathSource,

PathName, '-', [Depth])

LOAD * inline

[

Parent,Child

Z002,Z003

];

[settu_periasamy]

Hi,

May be like this?

Directory;
T1:
LOAD [Item Category],
Cost
FROM
[sample (4).xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join(T1)
LOAD [Item Category],Sum(Cost) as New Resident T1 Group by [Item Category] Order by [Item Category];

NoConcatenate
Final:
LOAD RecNo() as Rec,
if(Cost=New,[Item Category],Peek([Item Category])) as [Item Category],
if(Cost<>New,[Item Category]) as subcategory,
if(Cost=New,'',Cost) as Cost,
if(Cost=New,New) as Total_Val Resident T1 where Cost>0 Order by [Item Category] ;   
DROP Table T1;