Sum Of percentages and Percentage Calculation in Load Script

techvarun — Wed, 06 Mar 2019 15:22:10 GMT

Hi experts,

This may be a silly question and but I am quite confused here. below the scenario.

I have the below load script

LOAD * ,Interest/Balance as Rate INLINE [
Key, Interest, Balance
1, 10, 5
2, 20, 25
3, 30
4, , 40
5, 50, 98
6, 60, 17
7, , 99
8, 100, 1000
9, 120, 12
10, 140, 8
];

and populating a table like below

Key	Sum(Rate)	Sum(Interest)	Sum(Balance)
	34.44	530.00	1304.00
1	2.00	10.00	5.00
2	0.80	20.00	25.00
3	0.00	30.00	0.00
4	0.00	0.00	40.00
5	0.51	50.00	98.00
6	3.53	60.00	17.00
7	0.00	0.00	99.00
8	0.10	100.00	1000.00
9	10.00	120.00	12.00
10	17.50	140.00	8.00

But My expected table should look like below

Key	Sum(Rate)	Sum(Interest)	Sum(Balance)	Rates (expected)
	34.44	530.00	1304.00	2.460377358
1	2.00	10.00	5.00	2
2	0.80	20.00	25.00	0.8
3	0.00	30.00	0.00	0
4	0.00	0.00	40.00	0
5	0.51	50.00	98.00	0.510204082
6	3.53	60.00	17.00	3.529411765
7	0.00	0.00	99.00	0
8	0.10	100.00	1000.00	0.1
9	10.00	120.00	12.00	10
10	17.50	140.00	8.00	17.5

I know we can't sum rates but how do we acheive the correct rates at all levels.

Please advice. I must calculate the rates on the load script.

Re: Sum Of percentages and Percentage Calculation in Load Script

joseph_morales — Wed, 06 Mar 2019 16:20:21 GMT

Hi @techvarun ,

I copied your script into a new qlikview file and i got the following result:

The expression in rates column:

if(Dimensionality()=0,
sum(Balance)/sum(Interest),
sum(Rate)
)

Chart Properties -> number format -> Rates

Regards

Re: Sum Of percentages and Percentage Calculation in Load Script

bharathadde — Wed, 06 Mar 2019 16:23:58 GMT

Please find the attachment for solution

Hope this works for you

topic Re: Sum Of percentages and Percentage Calculation in Load Script in QlikView

Sum Of percentages and Percentage Calculation in Load Script

Re: Sum Of percentages and Percentage Calculation in Load Script

Re: Sum Of percentages and Percentage Calculation in Load Script