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.
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.
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.