Average Calculation

[arethaking]

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)

[MK_QSL]

Try this

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

[varshavig12 · Accepted solution]

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

[girirajsinh]

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)