Using multiple (parents) in over function

[Daniel Amrine]

I've been banging my head against the screen trying to understand the over function....

I have an identifier in the first column followed by year, followed by the month order as a number, and then the target continuos data i'm trying to analyze.

I set the APi as the identifier, because i've read the over function only works with categories.

 

Goal:

I want to average the last three months on the continous data over the APi and the Year so the end rsult would be a value of the last three months of production for each year for each identifier.

I've tried intersect, parent, filetering it down to those last three months...and I can't seem to get it.

Any help is much appreeciated!

[Fabian Duerr]

In a first step you can add a calculated column that checks if the month is within the last three months:

[iS_IN_LAST_3] =

[MONTH_ORD]>=(Max([MONTH_ORD]) OVER ([YEAR],[API]) - 2)This will evaluate to a boolean expression TRUE/FALSE

Then use this result in the next calculation toget the average:

[AVG_LIQUID_LAST_3] =

If([iS_IN_LAST_3],Avg([LIQUID]) OVER ([API],[YEAR]))This will leave some rows empty where the boolean expression returnedFALSE.

So, in a final step you fix this with:

First([AVG_LIQUID_LAST_3]) OVER ([API],[YEAR])I hope this is what you are looking for.