1 Reply Latest reply on Dec 7, 2012 12:19 PM by Tracy Rodgers

    Grand Total Average of a Rolling Window calculation

    Alec Guerenstein

      Hi everyone, I've been digging in the forum trying my best to avoid duplicating the question... As you can see, I haven't succeded and have to ask...


      I have a rolling calculation returning a value for every week (col's). Each value is based on the index() and previous 12 values, as follows (@Value_Needed is the target):


      1. @AbsErr = Abs(sum([A]) - sum([B]))

      2.a @AbsErr (Rolling13) = WINDOW_SUM(([@AbsErr]),-12,0)

      2.b @B (Rolling 13) = WINDOW_SUM(sum([B]),-12,0)

      3. @Value_Needed =

      if ( [@AbsErr (Rolling13)] > [@B (Rolling 13)] )

      then 0

      else (1 - ([@AbsErr (Rolling13)] / [@B (Rolling13)]))



      I get the values I need for every column within the partition...


      What I am looking for is a way to obtain an average of the rolling calculation (a single value) between two weeks (say... 20120041 and 20120044 in the picture) to detect outliers and rank them.

      this is the formula I am using:

      window_avg( (iif(avg([WEEK_ID])>=[Select Week From] and avg([WEEK_ID])<=[Select Week To]) ,[@Value_Needed],null))


      The above works like a charm, but I have to use it in the Level of Detail, since I can't find a way to use it without having the columns (weeks) displayed...


      Any ideas will be more than Welcome!


        • 1. Re: Grand Total Average of a Rolling Window calculation
          Tracy Rodgers

          Hi Alex,


          Would simply hiding the columns give you the desired view? Perhaps you can create a second calculated field that uses the [Select Week to] and/or [Select Week From] parameters similar to:


          if [Week_ID]=[Select Week From] or [Week_ID]=[Select Week to] then 'show' else 'hide' end


          Then, place this somewhere on the view, and right click and select Hide where it says hide.


          Hope this helps!