2 Replies Latest reply: Aug 16, 2018 10:24 AM by Felipe Andrade

# How to calculate average of sales

Hi Experts,

In the below table I have calculated 2017 YTD Sales column by using below expression

2017 YTD Sales

=Sum({\$<Year={'\$(=Year(Max(Date))-1)'},Month>}Sales)/1000000

2017 Monthly Sales

= Current month sales -Previous month sales I.e when we are in Feb its 10.53-4.87=5.66 its calculated like below

RangeSum(Sum({<Year={'\$(=Year(Max(Date))-1)'},Month>}Sales)/1000000,

-Above(Sum({<Year={'\$(=Year(Max(Date))-1)'},Month>}Sales)/1000000))

Now for 2017 Monthly Sales need to calculate the Average. Please help me to calculate the average for 2017 Monthly Sales.

• ###### Re: How to calculate average of sales

May be this

Avg({<Year = {'\$(=Year(Max(Date))-1)'}, Month>} Aggr(

RangeSum(Sum({<Year = {'\$(=Year(Max(Date))-1)'}, Month>}Sales)/1000000,

-Above(Sum({<Year = {'\$(=Year(Max(Date))-1)'}, Month>}Sales)/1000000))

, Year, Month))

• ###### Re: How to calculate average of sales

You need an IF statement.

like this :

IF(Year(Max(Date))-1<1,12,Year(Max(Date))-1)

in the final expression could be:

IF(Year(Max(Date))-1<1,

Avg({<Year = {'12'}, Month>} Aggr(

RangeSum(Sum({<Year = {'12'}, Month>}Sales)/1000000,

-Above(Sum({<Year = {'12'}, Month>}Sales)/1000000))

, Year, Month))  ,

Avg({<Year = {'\$(=Year(Max(Date))-1)'}, Month>} Aggr(

RangeSum(Sum({<Year = {'\$(=Year(Max(Date))-1)'}, Month>}Sales)/1000000,

-Above(Sum({<Year = {'\$(=Year(Max(Date))-1)'}, Month>}Sales)/1000000))

, Year, Month))

)

in this way you will have corrected the value when it is January.