By using this website, you agree to our Terms of Use (click here)
Hello! I've searched old posts trying to combine ideas to get this accomplished, but I'm struggling. I am trying to create a GI that has the start date of an event with total attendance (custom field) and add another column that shows the total attendance for prior year next to current year. I was hoping to pull same week prior year but I can't seem to get the right formula. So I have tried this formula. Can anyone help? I've tried several different options that now I've completely confused myself.
This is the latest formula I have tried:
=IIf(Year([CRPMTimeActivity.StartDate])=Year([StartDate]-1) And Month([CRPMTimeActivity.StartDate])=Month([StartDate]-12) And Day([CRPMTimeActivity.StartDate])=Day([StartDate]-365),[CRPMTimeActivity.UsrActualAttendance],0)
My parameters are:
StartDate = CRPMTimeActivity.StartDate = @YearStart
EndDate = CRPMTimeActivity.StartDate= @Today
I also attached screenshots.
Thanks in advance for any help. I'm sure it's something small I am missing!
For the week, what about joining over to the DateInfo Data Access Class (DAC) and grabbing DateInfo.WeekEnding?
For the prior year attendance, are you matching on only one event record? Or might you need to add multiple event records to come up with total attendance for the same week prior year?
Lastly, using DateAdd might make your formula simpler (except I'd replace [CRPMTimeActivity.StartDate] and [StartDate] with their respective DateInfo.WeekEnding values):
=IIf(DateAdd([CRPMTimeActivity.StartDate],'y',-1)=[StartDate],[CRPMTimeActivity.UsrActualAttendance],0)
But this formula wouldn't be able to add together multiple records. Hence the second question above.
Thanks, Tim. I didn't even think about DateInfo. I finally got around to working with the GI again and that works great!