WindowAVG with negative parameter

Question & Answer
,
1

Hello,
I need to create a calculated field with the following structure. I need to calculate the average of a field that starts 3 lines before the current line and ends one line before the current line.

However, Quicksight has a limitation: it does not allow us to pass a negative number as the end. In the end parameter, Quick Sight only allows positive numbers below the current line. I need to start from the previous line.

How can I get around this limitation?

This is an example of what I need:

windowAvg(sum({Camop1}),[date],3,-1)

2

Hi @COSTA

Unfortunately, windowAvg function does not support negative offsets, so it’s not possible to directly define a window that ends before the current row (e.g., start=3, end=-1).

To work around this, I’ve recreated the logic using lag functions to calculate the average of the previous 3 rows. Here’s how it works

Example:

Lag1 = lag(sum(Sales), [OrderDate ASC], 1)
Lag2 = lag(sum(Sales), [OrderDate ASC], 2)
Lag3 = lag(sum(Sales), [OrderDate ASC], 3)

Compute the average:

AVGLAG = (Lag1 + Lag2 + Lag3) / 3

image
image577×540 36.5 KB