topic Re: Script Equivalent of "Above" Chart function? in QlikView

Script Equivalent of "Above" Chart function?

Mon, 30 Dec 2013 16:37:37 GMT

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?

Gysbert_Wassenaar — Mon, 30 Dec 2013 16:38:32 GMT

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?

Mon, 30 Dec 2013 16:42:24 GMT

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?

swuehl — Mon, 30 Dec 2013 17:09:28 GMT

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?

Mon, 30 Dec 2013 17:26:54 GMT

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?

Mon, 30 Dec 2013 17:27:38 GMT

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