Running average for last 13 weeks only

[#1]

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.

[#2]

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

[#3]

actuallu my bad, it should be just 18

[#4]

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

[#5]

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

=sum({$<Week= {'>=$(=AddWeeks(Today(), -7))'}>} Value)

Thanks,

ANDY

[#6]

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

[#7]

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

[#8]

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.

[#9]

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.

[#10]

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.