Script Equivalent of "Above" Chart function?

[#1]

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

[Gysbert_Wassenaar]

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

[#3]

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

[swuehl]

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)?

[#5]

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

[#6]

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