Range Avg in Qlikview

[sreenivasareddy]

Hi Everyone , When i try to Calculate range Avg in gauge Chart , i am not getting desired value , kindly help me.

I am posting data in below ,

1)my field are Name,Score and Sales

2) in second step i done cumulative sum for both Score and Sales as shown  Score_cumulative  and  Sales_cumulative

3)next i divided  Score_cumulative/Sales_cumulative  the qlikview is giving  0.014072848 that means it's giving result of  17/1208.

4) but  my desired(Wanted ) result is range Avg of Column 6 i.e  0.015497469 in Gauge Chart, please help me.

Name	Score	Sales	Score_cumulative	Sales_cumulative	=Score_cumulative/Sales_cumulative
			17	1208	0.014072848	Qlikview Result
A	1	100	1	100	0.01
B	3	151	3	151	0.01986755
C	4	201	4	201	0.019900498
D	2	456	2	456	0.004385965
E	7	300	7	300	0.023333333
					0.015497469	Wanted Result

[dx_anupam]

Hello Sreenivas,

please click on the table and properties, from the expression select  =Score_cumulative/Sales_cumulative in that change the total mode from Expression total to "Average" of Rows. it will return you desired output.

Please find the attached screenshot for detail.

Regards,

Anupam

[sunny_talwar · Accepted solution]

Try this

Avg(Aggr(Score_cumulativeExpression/Sales_cumulativeExpression, Name, Score, Sales))

or just this

Avg(Aggr(Score_cumulativeExpression/Sales_cumulativeExpression, Name))

[sreenivasareddy]

Hi Anupam thanks for your response, but it's not working, and one more thing is in gauge chart Total mode is disabled,

find the attached screenshot.

[sreenivasareddy]

Hi sunny_Talwar, thanks for your Response ,i tried with your suggested expression ,but i am getting strange results, please find the attached screenshot.

[dx_anupam · Accepted solution]

HI Sreenivas,

I am getting correct result using the formula suggested by Sunny. Please find the attached screenshot.

Formula I have used is
=Avg(Aggr((Sum(Score)/Sum(Sales)),Name,Score,Sales))