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

    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
          Byron Van Wyk

          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

            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

             

             

             

             

             

             

             

             

             

             

             

             

             

             

             

             

             

             

             

             

             

              • Re: Running average for last 13 weeks only
                Byron Van Wyk

                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
                        Byron Van Wyk

                        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
                            Byron Van Wyk

                            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
                                John Witherspoon

                                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:
                                LOAD
                                Week as AsOfWeek
                                ,'Current' as WeekType
                                ,Week
                                RESIDENT Data
                                ;
                                CONCATENATE (AsOf)
                                LOAD
                                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.