Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone
I have trawled the forum but i can't find a solution to my problem. I am trying to return a previous month average for one of my fields which is named ApprovalDays.
My date field is [Request Period]
I have created a variable for vPreviousMonth - =month(AddMonths(Date(Today()),-1))
I am attempting the expression =if(month([Request Period]=vPreviousMonth),avg(ApprovalDays)) but i am returning the overall average of ApprovalDays and not the value for last month.
I'm sure that i'm probably missing something obvious but i cant work it out. Your help would be really appreciated.
Thanks
Danny
I would create a field in the script:
Month([Request Period]) as [Month Request Period]
and use it in the expression as follows(one of the two should work):
=Avg({<[Month Request Period] = {'$(vPreviousMonth)'}>} ApprovalDays)
=Avg({<[Month Request Period] = {'$(=vPreviousMonth)'}>} ApprovalDays)
Hope this helps
Best,
S
Function avg() must be outside of if()
=avg(if(month([Request Period]=vPreviousMonth),ApprovalDays))