Jump to content

Using multiple (parents) in over function

Daniel Amrine

Recommended Posts

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.



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!

Link to comment
Share on other sites

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:


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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...