topic Re: Average Calculation in QlikView

Average Calculation

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

Hi All,

I have the monthly Sales data per PersonID like below.

PersonID	Year	YearMonth	Sales
P1	2016	201601	1
P1	2016	201602	2
P1	2016	201603	3
P1	2016	201604	4
P1	2016	201605	5
P1	2016	201606	6
P1	2016	201607	7
P1	2016	201608	8
P1	2016	201609	9
P1	2016	201610	10

How to calculate YTD Sales Average?

Expression: sum(Sales)/count(distinct Month)

Re: YTD Average Calculation

MK_QSL — Thu, 12 Jan 2017 09:24:34 GMT

Try this

avg({<Year = {'$(=Max(Year))'}>}Sales)

Re: Average Calculation

varshavig12 — Thu, 12 Jan 2017 09:40:19 GMT

avg({<YearMonth={">=$(=YearStart(Max(YearMonth)))<=$(=Max(YearMonth))"}>} Sales)

Re: Average Calculation

girirajsinh — Thu, 12 Jan 2017 09:44:27 GMT

Hi Aretha

For your case of Avg.qvw,

try

sum({<Year={$(=Max(Year))},YearMonth={"<=$(=max(YearMonth))"}>} Sales)/count({<Year={$(=Max(Year))},YearMonth={"<=$(=max(YearMonth))"}>}distinct Month)