 Former Member

# Sum of a metric between two specific dates

Hey everyone, I have been trying to calculate a metric and have not had success thus far.

I would like to calculate the weekly average of a metric, with 8 weeks of history, and I need the dates to consider the weeks to begin on Monday and end on Sunday.

I began by creating a variable to identify the last Sunday (after first creating a variable to identify the current day) -

v.LastSunday

= If([v.DayNmofCurrDt]="Monday") Then RelativeDate([v.CurrDt];-1)

ElseIf([v.DayNmofCurrDt]="Tuesday") Then RelativeDate([v.CurrDt];-2)

ElseIf([v.DayNmofCurrDt]="Wednesday") Then RelativeDate([v.CurrDt];-3)

ElseIf([v.DayNmofCurrDt]="Thursday") Then RelativeDate([v.CurrDt];-4)

ElseIf([v.DayNmofCurrDt]="Friday") Then RelativeDate([v.CurrDt];-5)

ElseIf([v.DayNmofCurrDt]="Saturday") Then RelativeDate([v.CurrDt];-6)

ElseIf([v.DayNmofCurrDt]="Sunday") Then RelativeDate([v.CurrDt];0)

Then I created a second variable to identify the Monday on which the 8 week span would begin, based on the current day -

v.8WkBegin

= If([v.DayNmofCurrDt]="Monday") Then RelativeDate([v.CurrDt];-56)

ElseIf([v.DayNmofCurrDt]="Tuesday") Then RelativeDate([v.CurrDt];-57)

ElseIf([v.DayNmofCurrDt]="Wednesday") Then RelativeDate([v.CurrDt];-58)

ElseIf([v.DayNmofCurrDt]="Thursday") Then RelativeDate([v.CurrDt];-59)

ElseIf([v.DayNmofCurrDt]="Friday") Then RelativeDate([v.CurrDt];-60)

ElseIf([v.DayNmofCurrDt]="Saturday") Then RelativeDate([v.CurrDt];-61)

ElseIf([v.DayNmofCurrDt]="Sunday") Then RelativeDate([v.CurrDt];-62)

I believe those are what I need to identify when the 8 week period should begin and end based on the current day, to ensure I get the past 8 full Monday-Friday weeks.

Now I need to bring in the sum of a metric (v.sales) between those two dates - assuming I have the first part right, that's where I'm having trouble. I tried to do something along the lines of

v.8WkAvg

=Sum([v.sales]Where(RelativeDate(CurrentDate()Between([v.8WkBegin];[v.LastSunday]))))/8

but this formula isn't working. Any pointers would be greatly appreciated.