topic Re: How to use RangeAvg in back-end script in QlikView

How to use RangeAvg in back-end script

asifshah — Sat, 16 Nov 2024 01:26:32 GMT

I want to generate average values for the past 6 months

example table :

LOAD * INLINE [
Zone, Month, Value
East, Jan-2019, 98823
East, Feb-2019, 98372
East, Mar-2019, 47732
East, Apr-2019, 92838
East, May-2019, 92939
East, Jun-2019, 8822
East, July-2019, 991
East, Aug-2019, 13124
East, Sep-2019, 2324
East, Oct-2019, 231
East, Nov-2019, 1234
East, Dec-2019, 32442
];

Using the below function, it gives proper values,

RangeAvg(above(total sum(Value),0,5))

Output:

Zone	Month	Value	RangeAvg of Value
East	Apr-2019	92838	92838
East	Aug-2019	13124	52981
East	Dec-2019	32442	46134.66667
East	Feb-2019	98372	59194
East	Jan-2019	98823	67119.8
East	July-2019	991	48750.4
East	Jun-2019	8822	47890
East	Mar-2019	47732	50948
East	May-2019	92939	49861.4
East	Nov-2019	1234	30343.6
East	Oct-2019	231	30191.6
East	Sep-2019	2324	28892

However the same function doesn't work in back-end script.

Can anyone help me replicate this scenario in back-end?

Thanks.

Re: How to use RangeAvg in back-end script

marcus_sommer — Thu, 23 Jan 2020 10:51:13 GMT

Above() is an UI function and doesn't work within the script. With previous() and peek() there are interrecord-functions which could be used in the script - but they could pick only a single value and not a range of values. This means you need to add multiple ones - this may (with the help of an additionally variable) look like:

set eP = "peek('Value', $1)";

table:
load *, rangeavg(Value, $(eP(-1)), $(eP(-2)), $(eP(-3)), $(eP(-4))) as AVG
resident Source order by Month;

Important is that you sort the (resident) load appropriate and if there are multiple Zone's or other relevant fields available you need some if-logic to include to consider them (the more circumstances needs to be catched the more benefit has the above created variable to keep the statement small and clear).

- Marcus

Re: How to use RangeAvg in back-end script

Brett_Bleess — Wed, 19 Feb 2020 20:31:50 GMT

Did Marcus' post help you with your use case? If so, please do not forget to return to your thread and on his post, use the Accept as Solution button to mark it as the solution which will also give him credit for the assistance and let other Members know it did help too. If you are still working upon things, please leave an update and if you figured out an alternate solution, please consider posting and marking that as the solution.

Regards,
Brett