average comparison if statement

[#1]

I am wondering what is the best way to show a comparison to the previous 13 weeks.

I want to show the most recent 13 week average but cannot get my if statement correct - can someone please help?

count(if(week<max(week) and week>=max(week)-14 and answered='Yes',answered))

[MK_QSL]

Something like below

=SUM({<WeekNum = {'<=$(Week(Today()))>=$(=Week(Today())-13)'}>}Amount)/13

Change your field names accordingly...

[#3]

i suppose to make max(date) in a script as a variable:

LET vMaxWeek = max(week)

LET vLowerWeek = max(week)-14

then use the variables in set analysis, beacuse its more efficent than if statement

[Anonymous]

try to define variable for the max week,

vMaxWeek = Max(week)

and use set analysis expression

=$(='count({<week={"<'& vMaxWeek & ' >=' & vMaxWeek-14 & '"}, answered={'Yes'}>}answered)')

[#5]

Guys think about what you're saying..

In Week 8 F.E.

max(Week)-14 would be -6...

I'll provide you with a more efficient solution ASAP.

-Patrick

[#6]

I think this might be along the right steps but unfortunately I don't know enough about variables or set analysis.

The graph I am trying to plot the average on is has weeks as a dimension. I want a sold line across the graph that shows what the average weekly number of calls answered have been over the past 13 weeks - regardless of what weeks the user selects

[#7]