12 Replies Latest reply: Jun 7, 2011 10:18 AM by Byron Van Wyk

# Running average for last 13 weeks only

Hi,

I'm trying to display a running average for the last 13 weeks but I need to display a value for all 13 weeks.  Currently my graph is not displaying some of the weeks because there is no data for that week.  Sample data is as follows:-

Week        Value     Running Average 13 weeks

1               20                  20

2               25                  22.5

3               24                  23

4               30                  24.8

5               18                  23.4

6                0                  19.5

7               19                  19.4

8               26                  20.3

9               24                  20.7

10             0                    18.6

11             15                  18.3

12             20                  18.4

13             25                  18.9

14             18                  20.3

15             0                    20.3

16             20                  21.8

17             25                  23.8

18             19                  25.2

19             28                  27.4

20             14                  28.5

My calculation is working correctly but for weeks where there is no value (eg week 15) there is no value on the chart.  If I select 'Show all Values' in the Dimension tab all 20 weeks are displayed.

Can anyone advise how I can show a running average for the last 13 weeks only that displays a value for all 13 weeks.

Thanks,

Eamonn.

• ###### Running average for last 13 weeks only

Hey, I just checked your average and it is not correct. For week 20, your average should be 18.77 if you are averaging the last 13 weeks and this is not the case. Busy working on how you can do this correctly first and then how to display that on your chart

• ###### Running average for last 13 weeks only

actuallu my bad, it should be just 18

• ###### Re: Running average for last 13 weeks only

Lol, this is as far as I got. Hopefully someone else will be able to get this to work.

All values on the attachment from 13 and upwards are correct. Weeks 12 and down are incorrect. I tried to fix this but with no success. Hopefully this helps a little bit

Cheers,

Byron

• ###### Running average for last 13 weeks only

Hello Eamonn,

In your expression can you try something like Max(Week) -7 if its always 20 weeks if not then, it should be something like....

Thanks,

ANDY

• ###### Re: Running average for last 13 weeks only

Hello Andy,

Please could create a test document and show us both how that expression is suppose to work. Could not get it to do what I think Eammon is trying to achieve.

Cheers,

Byron

• ###### Running average for last 13 weeks only

Hello Byron,

The expression I have mentioned above is derived from this one:

=sum({\$<TIME_PERIOD_START_DATE = {'>=\$(=AddYears(Today(), -1))'},TIME_PERIOD_PERIOD=,TIME_PERIOD_YEAR= >} WK_SALES_GROSS_REV)/1000

I have a similar requirement for 52 rolling weeks at anytime and the above expression sucessfully works for me... can you try and work around this as i might not be able to spend more time on this.

Thanks

ANDY

• ###### Running average for last 13 weeks only

Hi Byron and Andy,

Thanks for your time on this.  I'm using the following function to calculate my 13 week average and the figures are correct:

rangeavg(above(sum(Age),0,13))

There is a typo error in the sample data I sent, apologies.

My issue is displaying only 13 weeks of data rather than all 20 weeks.

Thanks,

Eamonn.

• ###### Re: Running average for last 13 weeks only

Hi Byron and Andy,

Thanks for your time on this.  I'm using the following function to calculate the 13 week average.  There is an error with the sample data I sent you, apologies, but I'm happy my values are correct, the issue is displaying only 13 weeks of it:

rangeavg(above(sum(Age),0,13))

Thanks,

Eamonn.

• ###### Re: Running average for last 13 weeks only

Hi Eammonn,

Ill try work with what you have. Andy, there is no function called AddWeeks. There's AddMonths and AddYears like you have used but not AddWeeks.

• ###### Re: Running average for last 13 weeks only

HI Eammonn,

I have tried my best to use the knowledge available to me to do this, but cannot seem to find a nicer/cleaner way. Please see attached document.

• ###### Re: Running average for last 13 weeks only

I have an example of my own that uses rangeavg(above(Price,0,50)) to get a 50 day rolling average.  I think it's a clean solution, and may be good enough in practice.

However, it's sensitive both to sort order and to selections.  If you sort in the opposite order, suddenly you're looking at the future 13 weeks.  If you select a specific week, it won't show you the 13 week rolling average for that week, but simply that week's value.  A solution that fixes those problems is to use an AsOf table to manage your rolling period.

AsOfWeek, WeekType, Week
1,Current,1
1,Rolling,1
2,Current,2
2,Rolling,2
2,Rolling,1
3,Current,3
3,Rolling,3
3,Rolling,2
3,Rolling,1
etc.

Now build a pivot table like this:

Dimension 1 = AsOfWeek
Dimension 2 = WeekType // move to the top
Expression  = avg(Value)

That doesn't allow sorting, I suppose.  For a straight table it's a little more complicated, but not overly so:

Dimension = AsOfWeek
Expression 1 = avg({<WeekType={'Current'}>} Value)
Expression 2 = avg({<WeekType={'Rolling'}>} Value)

To generate the table above for the sample file:

AsOf:
Week as AsOfWeek
,'Current' as WeekType
,Week
RESIDENT Data
;
CONCATENATE (AsOf)
Week as AsOfWeek
,'Rolling' as WeekType
,Week + 1 - iterno() as Week
RESIDENT Data
WHILE iterno() <= 13
AND Week + 1 - iterno() > 0
;

I assume the real situation is more complicated, though, and wouldn't use this exact script.  But the idea is you generate the table in script.  Eh, I guess I might as well post it now that I've built it to make sure I wasn't getting anything wrong.

• ###### Re: Running average for last 13 weeks only

All your responses that I read John are really insightful and helpful. On behalf of the community and noobs like me, thanks for always taking the time to clearly explain the method behind your madness

Cheers,

Byron