topic Re: How to calculate a ratio in a Hierarchy table in QlikView

How to calculate a ratio in a Hierarchy table

dripet — Wed, 25 Nov 2020 16:16:04 GMT

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 Child	CHILD	Code Parent	Amount	Ratio % ( On Parent)	Ratio % (Child Cat)
A	x	RE	60	A/A (I need this on A not on RE	A/A
AI	x_1	A	60	AI/A	AI/A
AI_1	x_1_1	AI	60	AI_1/AI	AI_1/A
AI_1_1	x_1_1_1	AI_1	20	AI_1_1/AI_1	AI_1_1/A
AI_1_2	x_1_1_2	AI_1	40	AI_1_2/AI_1	AI_1_2/A
B	y	RE	40	B/B	B/B
BI	y_1	B	40	BI/B	BI/B
BI_1	y_1_1	BI	40	BI_1/BI	BI_1/B
BI_1_1	y_1_1_1	BI_1	25	BI_1_1/BI_1	BI_1_1/B
BI_1_2	y_1_1_2	BI_1	15	BI_1_2/BI_1	BI_1_2/B
RE	Z		100	100%

Re: How to calculate a ratio in a Hierarchy table

Saravanan_Desingh — Fri, 22 May 2020 21:14:29 GMT

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;

Re: How to calculate a ratio in a Hierarchy table

Saravanan_Desingh — Fri, 22 May 2020 21:15:31 GMT

Re: How to calculate a ratio in a Hierarchy table

dripet — Sat, 23 May 2020 14:39:38 GMT

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.

Re: How to calculate a ratio in a Hierarchy table

Saravanan_Desingh — Sat, 23 May 2020 15:44:51 GMT

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