Skip to Content
0
Former Member
Apr 15, 2014 at 04:41 PM

Rolling 30 Day Calculation Based on the Weekend Date

176 Views

Hi Guys,

I am trying to calculate a sum of rolling 30 day based on the weekend date. The table I am going to query has a date column that only has weekend date(Saturdays only). Based on that I have to calculate the formula for rolling 30 days. I am using the formula to RelativeDate formula to do this calculation.

ToDate(FormatDate(RelativeDate(CurrentDate();-DayNumberOfWeek(CurrentDate() - this formula is to get the last Sat of the week.(Previous Week)

=RelativeDate([Previous_Week];-30) This is to get the rolling 30 days based of the last Saturday of the week.

Here is where I face the problem and would need your suggestion -

when I do a sum using the previous date and rolling 30 day I am getting an error

=sum([Acc]) Where [Week Ending] Between([Previous_Week];[Rolling 30 Days])

Error- Missing or bad operand in Where expression.

Is there any other formula I could use?

Thanks for your help in advance,

Jitan