How to calculate average of sales

[mahitham]

Hi Experts,

Can any one please help me on below requirement.

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.

Thanks in advance

[sunny_talwar]

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

[Anonymous]

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.