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.