Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
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)
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
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)
This expression should work:
avg({<[Non Service_F]={"<$(=DATE(MonthStart(Today(1))))"}>}Total_F)
"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.