Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
dripet
Contributor
Contributor

How to calculate a ratio in a Hierarchy table

Hi, 

I'm working on a straight Table with Child as dimension and exp. Sum(amount) and I need to perform the below calculation, Any suggestion on how can I peek up the value of the Parent ( or of any level that I need to perform the calculation)?  Or any other suggestion on how to perform these ratios?

 

Code ChildCHILDCode ParentAmountRatio %
 ( On Parent) 
Ratio % (Child Cat)
AxRE60A/A (I need this on A not on REA/A
AIx_1A60AI/AAI/A
AI_1x_1_1AI60AI_1/AIAI_1/A
AI_1_1x_1_1_1AI_120AI_1_1/AI_1AI_1_1/A
AI_1_2x_1_1_2AI_140AI_1_2/AI_1AI_1_2/A
ByRE40B/BB/B
BIy_1B40BI/BBI/B
BI_1y_1_1BI40BI_1/BIBI_1/B
BI_1_1y_1_1_1BI_125BI_1_1/BI_1BI_1_1/B
BI_1_2y_1_1_2BI_115BI_1_2/BI_1BI_1_2/B
REZ 100100% 

 

1 Solution

Accepted Solutions
4 Replies
Saravanan_Desingh

Are you looking something like this?

H:
Hierarchy([Code Child],[Code Parent], Amount,ParentAmount)
LOAD [Code Child], 
     CHILD, 
     [Code Parent], 
     Amount
FROM
[C:\Users\sarav\Downloads\QVwork\hierqvcomm.xlsx]
(ooxml, embedded labels, table is Sheet2);

tab1:
LOAD *, (Amount/ParentAmount)*100 	As [Ratio % (On Parent)]
	  , (Amount/Amount1)*100		As [Ratio % (Child Cat)]
Resident H
;

Drop Table H;
Saravanan_Desingh

commQV66.PNG

dripet
Contributor
Contributor
Author

Hi, 

thank you for the response, I preferred to have these ratios as expressions function in the straight table and not as new dimensions, however I can try to implement this solution in my model.

Saravanan_Desingh

Please accept it as the solution, if it satisfies your requirement.