Last 4 Week Average

[Anonymous]

Trying to calculate Avg of last four (available) weeks in a straight chart that has two dimensions:

Avg(Aggr(Sum({<WeekNum = {'<=$(=vCurrWeek)>$(=vCurrWeek - 4)'}> } sales), WeekNum))

Where, WeekNum comes from Calendar table and vCurrWeek is the latest week available.

By doing this, I get values for first three rows only and rest of the rows as blank.

[jvitantonio]

What are the 2 dimensions you are using? Try in your set analysis >= otherwise you are evaluating for your vCurrWeek and vCurrWeek - 3

Avg(Aggr(Sum({<WeekNum = {'<=$(=vCurrWeek)>=$(=vCurrWeek - 4)'}> } sales), WeekNum))

J

[Anonymous]

Two Dimensions are Client and Product.

[MK_QSL]

We need to know which two dimensions are used....

try below code....

=AVG(AGGR(SUM({<WeekNum = {">=$(vCurrWeek-3) <= $(vCurrWeek)"}>}sales), WeekNum))

[MK_QSL]

Or use

=AVG(AGGR(SUM({<WeekNum = {">=$(vCurrWeek-3) <= $(vCurrWeek)"}>}sales), WeekNum), Client, Product)

[Anonymous]

I tried:

=Avg(Aggr(Sum({<WeekNum =  {">=$(vCurrWeek-3)<=$(vCurrWeek)"}>} Sales), Client, Product, WeekNum))

it brings back results but they don't seem correct.

[MK_QSL]

Try This

=Aggr(Avg(Sum({<WeekNum =  {">=$(vCurrWeek-3)<=$(vCurrWeek)"}>} Sales)), Client, Product, WeekNum)

[vivientexier]

Be sure your week number is unique. I mean ; does your week number restart to 1 at the beginning of the year ?

[Clever_Anjos]

Could you post a sample of your data? It would be easier to help

[Gysbert_Wassenaar]

That sounds like you want a rolling 4-week average and you're using week as a dimension in your chart. If that's correct I think this document can help you understand how to do that: Calculating rolling n-period totals, averages or other aggregations