Help with average

[joshrussin]

Hey guys,    

I am trying to get the average of Total_F for the year, NOT including this month.

The correct number I need to achieve is:  306,922

It is also including the 0 and making my answer 245,538

Need some help getting current year, excluding current month. Here is what I am trying.

=(Avg({$<Month = {"$(!=Month(Today()))"}, Year = {"$(=Year(Today()))"}>} Total_F))

[manas_bn]

Hi Joshua,

Can you create a "MonthYear" Column in the back end with MMM-YYYY format? Then you can use the below expression.

=Avg({$<MonthYear -={"$(=Date(MonthStart(Today()), 'MMM-YYYY'))"}>} Sales)

Below is the script I used:

//Temp Table

test:

load * inline

[Date, Sales

1/1/2017,10

2/2/2017,10

3/3/2017,10

4/4/2017,10

5/5/2017,10

6/6/2017,10

7/7/2017,2];

// Resident load with MonthYear Column

test1:

load

Date,

month(Date)&'-'&year(Date) as MonthYear,

Sales

resident test;

// drop temp table

drop table test;

Result:

Hope this helps!

Manas

[sunny_talwar · Accepted solution]

Try this may be

Avg({$<[Non Service_F] = {"$(='>=' & Date(YearStart(Max([Non Service_F])), 'M/D/YYYY') & '<' & Date(MonthStart(Max([Non Service_F])), 'M/D/YYYY'))"}, Month, Year>} Total_F)

[jcampbell474]

This expression should work:

avg({<[Non Service_F]={"<$(=DATE(MonthStart(Today(1))))"}>}Total_F)

[satishsure]

"Monthval"  is calculated column contains month names in "MMM" format.Basically comparing the same values in set analysis

=Avg( {$ < Monthval -={"$(=Date(Max(Non_Service_F),'MMM'))"}  >} Total_F).

find the below link for more details.

Excluding values in Set Analysis