Count Last 4 Week AVG

[Anonymous]

Hi, i have a question,

I have a data like this:

Area	Week	QTY
America	1	5
America	2	10
America	3	20
America	4	30
America	5	40
Japan	1	1
Japan	2	1
Japan	3	2
Japan	4	2
Japan	5	3

I want to create a straight table that count the total qty and also the average for last 4 weeks in each area.

The result i expect is like this:

Area	Week	Sum(QTY)	Avg Last 4 Week
America	1	5	0 --> zero bcs 0 row above
America	2	10	0 --> zero bcs only 1 row above
America	3	20	0 --> zero bcs only 2 rows above
America	4	30	0 --> zero bcs only 3 rows above
America	5	40	(5+10+20+30)/4 --> Total Week1-4 divided by 4
Japan	1	1	0
Japan	2	1	0
Japan	3	2	0
Japan	4	2	0
Japan	5	3	(1+1+2+2)/4 --> Total Week 1-4 divided by 4

Is it possible to make it ?

Really need and appreciate your help

Thanks in advance.

I also attach my sample file and data

[Kushal_Chawda]

=if(Area<>above(Area),0,rangeavg(above(total Sum(QTY),0,4)))

[tamilarasu]

Lejours,

Try this,

If(Below(Area)<> Area,Sum(Total <Area> {<Week -= {"$(=MAX(Week))"}>}[QTY])/ (Count(Total <Area> Week)-1),0)

[Kushal_Chawda]

What if there is 6 and 7 week also?. I think he is asking for last 4 weeks.

[varshavig12]

if(Week>4,rangeavg(above(total avg( QTY ),1, 4 )),0)

[varshavig12]

Dimension: Area

                 Week

Expression:

sum(QTY)

if(Week>4,rangeavg(above(total avg( QTY ),1, 4 )),0)

[tamilarasu]

I thought it was a sample data. I guess the OP needs a dynamic expression. Lets wait for the reply.

[varshavig12]

if(Week>4,rangeavg(above(total sum( QTY ),1, 4 )),0)

Please use sum, instead of avg.

[pho3nix90]

I am not sure if the week number is month weeks, or year weeks. (52 weeks), below should be dynamic and do avg every 5 weeks regardless

Try this:

Script:

Directory;
Data:
LOAD Week,
     QTY,
     Area
FROM
[data avg.xlsx]
(ooxml, embedded labels, table is Sheet1);
Left Join
LOAD
*,
if(Mod(Week,5) = 0,
  Peek('AccumQTY'),
  If( RecNo()=1 or Previous(Mod(Week,5) = 0),QTY
  , QTY+Peek('AccumQTY'))
  ) as AccumQTY ;
LOAD 
*
Resident Data
Order By Area, Week;

in chart expression

=Sum(if(Mod(Week,5)=0, AccumQTY)/4)