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

# Script Equivalent of "Above" Chart function?

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

• ###### Re: Script Equivalent of "Above" Chart function?

In the script you can use the Previous and Peek functions. See this document for an explanation of when to use one or the other: Peek() vs Previous() – When to Use Each

That said you will probably want to use another approach. See this document: Calculating rolling n-period totals, averages or other aggregations

• ###### Re: Script Equivalent of "Above" Chart function?

Thanks but as I mentioned in my original post, I know that I could use peek/previous to calculate and store the values in memory but what I'm asking for here is an alternative to that.

Roz

• ###### Re: Script Equivalent of "Above" Chart function?

Hm, can you elaborate a bit more what's wrong with storing the values in memory?

Or with using the peek/previous functions in the script?

Or with all the different suggested approaches following Gysbert's second link (have you even read his blog post within the two minutes)?

• ###### Re: Script Equivalent of "Above" Chart function?

Hi

It's not that there is anything wrong with using the peek/previous to load & store every possible rolling total/average value that will be needed in the app - I'm just asking if anyone knows of a simpler alternative to this on the scripting side like the "Above" function in charts because this is such a neat and simple way to get the same result, that does not require loading of any extra data into memory.

I didn't read Gysbert's second link before my reply, because it was not there in his original post - I assume he edited it following my comment that I was already aware of the peek/previous functions.

thanks

• ###### Re: Script Equivalent of "Above" Chart function?

Thanks for the second link Gysbert - I had read it before but appreciate you taking the time.