5 Replies Latest reply: Dec 30, 2013 12:27 PM by Roz Meehan RSS

    Script Equivalent of "Above" Chart function?

    Roz Meehan

      Hi,

      In my app dashboard, I have a Pivot Table showing Summary Stats for today and a line chart showing the trend of Qty plotted against a rolling average Qty over time.

       

      In the chart, to calculate the rolling average value for the last 50 periods ([Date] dimension), I have a variable expression defined in the script as follows.  This is working perfectly because the Date dimension is shown on the graph, so I can use the Above function to retrieve the last 50 categories in the Date dimension and sum and then Avg the Qty for them.

       

      RangeAvg(above(sum({$<

      [Date]={"<=$(vDimDateFilter)"}
      ,WeekdayFlag={"Y"}
      ,[DimA]={"$(vDimAFilter)"}
      >} [Qty]),0,50))

       

      However, in my Pivot Table, the "Above" function wont work as I am not displaying the Date dimension in this table.  Here I just want to show the last value of this calculation - essentially the Avg value as at today for the last 50 Time periods.

      Is there any way to define this with a script variable expression like the one above (excuse the pun!)? 

      I know that I can resident load my raw data and use peek/previous to calculate and store the running total & avg values for the last 50 working days, but I want to avoid having to do that if possible as it will get a bit complex, with trying to determine working days etc.

       

      Thanks!

      Roz