Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Specialist
Specialist

Re: How to calculate a ratio in a Hierarchy table

4 Replies
Highlighted
Specialist
Specialist

Re: How to calculate a ratio in a Hierarchy table

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;
Highlighted
Specialist
Specialist

Re: How to calculate a ratio in a Hierarchy table

Highlighted
Contributor
Contributor

Re: How to calculate a ratio in a Hierarchy table

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.

Highlighted
Specialist
Specialist

Re: How to calculate a ratio in a Hierarchy table

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