Average of previous month

[#1]

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

[sunny_talwar]

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

[Anonymous]

Function avg() must be outside of if()

=avg(if(month([Request Period]=vPreviousMonth),ApprovalDays))