3 Replies Latest reply: Nov 1, 2011 7:18 PM by Stefan Wühl RSS

    Get the last value from a rolling average

      Hi

       

      I currently have a line chart with the year/month as the dimension and the following rolling average expression plotted.

       

      =rangeavg(above((kpi_1a.company_result),0,12))

       

       

      I have now been ask to plot the latest months, rolling average value, therefor the "last" value from the above expression on a gauge chart. I'vebeen playing around with max() statements and calculated dimensions and if's inthe expression but as of yet I’ve had no joy.

       

      I'd appreciate any help you people might be able to offer.

       

       

      Cheers

      Chris

        • Get the last value from a rolling average
          Stefan Wühl

          Hi Chris,

           

          try something like

           

          =max(aggr(rangeavg(bottom((kpi_1a.company_result),1,12)),YearMonth))

           

          So you introduce a aggregation table using the advanced aggregation function aggr(), then use bottom to get the last 12 column segment values and feed them into rangeavg. You should get the same average value for all YearMonth values of your dimension in this embedded advanced aggregation table, so I just pick one with max().

           

          Hope this helps,

          Stefan

            • Get the last value from a rolling average

              First many thanks for your reply.

               

              When I only select 12 months worth of data the maths behind the scenes seems to work with your expression.

               

              However to get a true rolling average I'm selecting 24 months data as in the 1st item of my 12 months is an average of the previous 12 month's averages etc.

               

              And when I select the full 24 periods it puts the single value calculated by the expression as incorrect (93% instead of 91.5% not that it really matters, I image it's down to the aggrigation which i havn't used before so not quite clear on what it is doing)

               

              After reading through the reference manual i was wondering if i could use the firstsortedvalue() funtion ? However i don't have any experience of this it seems it should fit my needs, i havnt got the syntax right yet though. Do you think this could be a viable solution ?

                • Get the last value from a rolling average
                  Stefan Wühl

                  Hi Chris,

                   

                  I haven't fully understood when you say 'as in the 1st item of my 12 months is an average of the previous 12 month's averages'.

                   

                  Does your expression in your original post give you the correct numbers? How many dimensions do you use? If you use more than one dimension, like Year and Month instead of YearMonth, I imagine you get problems with the column segments and probably need to add a total qualifier to the bottom function.

                   

                  I would be cautious to imply that the difference in the results is due to the aggregation, maybe, but probably not. So let's take a close look.

                   

                  Could you post a simplified sample app here? Upload is available in advanced editor. If not, could you give some more details or post at least a small inline table with sample data here?

                   

                  Regards,

                  Stefan

                   

                  P.S: Regarding the firstsortedvalue() function, yes, maybe you could use it, but also not sure how. What is your idea, could you explain it in words what you want to do?

                  And, you could probably replace the aggr() and the bottom function with a set expression, something like this:

                  =avg( {<YearMonth={">=$(=max(YearMonth)-11)<=$(=max(YearMonth))"}>}  kpi_1a.company_result)