Calculating sum of a moving range

[AsmJuv2004]

Hi Everyone,

I have a data like this.

Record Time	Parameter	Moving Range
1/31/24 13:15:18	43.00
1/31/24 10:21:47	22.00	21.00
1/31/24 10:21:33	31.00	9.00
1/31/24 10:21:19	28.00	3.00
1/31/24 10:18:56	56.00	28.00
1/31/24 10:18:39	47.00	9.00
		70.00	Total

I want to write an expression in the front end to get the total of a moving range as highlighted in bold. Any help would be appreciated. Thanks

[Braveen]

What is the expression used for moving range?

[AsmJuv2004]

Its the absolute difference between each consecutive rows for Parameter column. Apologies for not being clear.

[JonnyPoole]

The moving range value can be calculated using the above() function. It grabs previous values from inside the chart. 

In your case:

SUM(Above([Parameter]) - [Parameter])

Check out the full features of this helpful function here:

https://help.qlik.com/en-US/sense/February2024/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/Inter....

[AsmJuv2004]

HI @JonnyPoole , thanks for the help. The formula you shared doesn't seem to be doing the trick as I get the error the sum cannot be used outside above.

So I tried interchanging it and got the values for each record. Below is the formula

Fabs(Above(SUM(original_val)) - Sum(original_val))

But my further question is how do I get the total of this? I tried few things with Rangesum and Aggr but its not working for me.

[JonnyPoole]

Try using the column() function to subtract the values and then enable the 'totals' and set it to sum. You can also move the totals to show at the bottom on the presentation tab properties of the table. 

Here is the result you asked for with the expressions in the headers:

[AsmJuv2004]

@JonnyPoole , thanks for the reply. This method gives me a table with the total. I need to display the total of the moving range in a KPI visual. So I need just the total. Can we do this using expression?

[JonnyPoole · Accepted solution]

You can do that.  Normally above() works on rows in a table or rows in the table equivalent of the chart.  

But you can also make it work on an artificial table of values that gets generated on the fly by the aggr() function. 

This is what I used.  Note the use of an extra argument in the aggr() function to ensure the artificial table of values is sorted in descending order on [Record Time] to match the visual sort example you gave. That way you know that the above() row is the value from timestamp immediately preceding. 

Here is the community reference: 

https://community.qlik.com/t5/Design/How-I-used-Aggr-and-Above-in-a-KPI-Object/ba-p/1470932

sum(aggr( (above(sum(Parameter)) - sum(Parameter)) ,([Record Time], Numeric, Descending) ))

[JonnyPoole]

To avoid AGGR() and any performance hits that may arise you also do a row by row level pre-calc in the load script: