topic Re: Range Avg in Qlikview in QlikView

Range Avg in Qlikview

sreenivasareddy — Thu, 13 Dec 2018 10:45:55 GMT

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

Re: Range Avg in Qlikview

dx_anupam — Thu, 13 Dec 2018 11:06:56 GMT

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

Re: Range Avg in Qlikview

sunny_talwar — Thu, 13 Dec 2018 12:13:52 GMT

Try this

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

or just this

Avg(Aggr(Score_cumulativeExpression/Sales_cumulativeExpression, Name))

Re: Range Avg in Qlikview

sreenivasareddy — Thu, 13 Dec 2018 13:04:07 GMT

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.

Re: Range Avg in Qlikview

sreenivasareddy — Thu, 13 Dec 2018 13:09:25 GMT

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

Re: Range Avg in Qlikview

dx_anupam — Thu, 13 Dec 2018 13:20:00 GMT

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))