topic Re: Last 4 Week Average in QlikView

Last 4 Week Average

Anonymous — Tue, 08 Oct 2013 15:02:45 GMT

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 — Tue, 08 Oct 2013 15:19:59 GMT

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))

Anonymous — Tue, 08 Oct 2013 15:27:56 GMT

Two Dimensions are Client and Product.

MK_QSL — Tue, 08 Oct 2013 15:30:58 GMT

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

try below code....

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

MK_QSL — Tue, 08 Oct 2013 15:38:02 GMT

Or use

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

Anonymous — Tue, 08 Oct 2013 17:06:03 GMT

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 — Tue, 08 Oct 2013 19:49:35 GMT

Try This

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

vivientexier — Tue, 08 Oct 2013 22:17:36 GMT

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

Clever_Anjos — Tue, 08 Oct 2013 22:19:22 GMT

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

Gysbert_Wassenaar — Wed, 09 Oct 2013 06:07:29 GMT

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