Replicate Above inthe script

[olgaavalos]

Hi,

I need to replicate the funtion: "Above" into the script. Please, help!

The formula is:

RangeStDev(above(sum(Sales),0,4))

[neerajthakur]

Trying using previous

[olgaavalos]

it is not work because I need to move 4 places, not 1.

[Clever_Anjos]

I´m a bit confused here, do you wan to move this calculation to a script level? You mean you want to pre calculate the values and have them ready in the data model?

[olgaavalos]

Yes, it is correct. 

[Clever_Anjos]

Above is not a script level function. There is a similar function called Peek() (https://help.qlik.com/en-US/sense/November2022/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecor...) that might be used implement what you want to achieve

This pseudo code might be what you need, but keep in mind you need to adjust to your needs

Fact:
Load
<other fields>,
Month,
Sales
From yourqvd.qvd(qvd);

AggrTableTmp:
Load
Month,
Sum(Sales) as sumsales
Resident Fact;

AggrTable:
Load
Month,
RangeSum(
sumsales,
Peek('sumsales',-1),
Peek('sumsales',-2),
Peek('sumsales',-3),
Peek('sumsales',-4)
) as sumsales4months
Resident AggrTableTmp
Order by Month;
Drop table AggrTableTmp;