Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi The expression below is giving me the max YearMonth but I cannot get it to work for the Max YearMonth -1
Any help appreciated
if(isnull(num((((Avg({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth))"}>}ActualValue)))), vNumber)),Null(),
num((((sum({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth))"}>}ActualValue)))), vNumber))
What is the YearMonth format? 201606 for example?
it is made by the text below in the script and is YYYY,MM
MonthName(Date(makedate(ReferredYYYY,ReferredMM))) as YearMonth,
I recommend you to create Variable for that
For prev month you can try this too
if(isnull(num((((Avg({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth)-1)"}>}ActualValue)))), vNumber)),Null(),
num((((sum({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth)-1)"}>}ActualValue)))), vNumber))
OR
if(isnull(num((((Avg({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Addmonths(Max({<[HRPrimaryCare]={7}>}YearMonth)),-1)"}>}ActualValue)))), vNumber)),Null(),
num((((sum({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Addmonths(Max({<[HRPrimaryCare]={7}>}YearMonth)),-1)"}>}ActualValue)))), vNumber))
Note: AddMonth(Expression, -1) will gives the previous month Data. If not, better to offer you logic if you shared the application
addmonths
How about this:
Avg({$<HRPrimaryCare = {7}, ReferredYYYY, YearMonth = {"$(=MonthName(AddMonths(Max({<[HRPrimaryCare] = {7}>} YearMonth), -1)))"}>} ActualValue)
and for this month, you might be able to simplify your expression to this
Avg({$<HRPrimaryCare = {7}, ReferredYYYY, YearMonth = {"$(=MonthName(AddMonths(Max({<[HRPrimaryCare] = {7}>} YearMonth), 0)))"}>} ActualValue)