Jump to content
We've recently updated our Privacy Statement, available here. ×

Custom expression using OVER and INTERSECT


Robert Rümmler

Recommended Posts

I have a simple table with three columns (see attached screenshot and Excel table):

 

Period_String in the format MM-YYYY, with the timeline progressing in half-yearly increments (e.g. 2015-12 or 2016-06)

Name of bank

IA.02 Zins und Diskontertrag (1.1)

 

I wish to calculate the growth rate of the value column "IA.02 Zins und Diskontertrag (1.1)" between different time periods, ranging from 12 months to 36 months.

So far, I have been able to calculate the growth rate (as a percentage) between consecutive periods (e.g. between 2015-12 and 2016-06) as follows within a calculated column:

CUSTOM EXPRESSION TO CALCULATE GROWTH % - CURRENT PERIOD COMPARED TO PREVIOUS PERIOD

100 * (Sum([iA.01 Zins- und Diskontertrag (1.1)]) over ([Period_String],[Name of bank]) -Sum([iA.01 Zins- und Diskontertrag (1.1)]) over (Intersect([Name of bank],Previous([Period_String])))) /Sum([iA.01 Zins- und Diskontertrag (1.1)]) over (Intersect([Name of bank],Previous([Period_String])))

100 * (Sum([iA.01 Zins- und Diskontertrag (1.1)]) over ([Period_String],[Name of bank]) -Sum([iA.01 Zins- und Diskontertrag (1.1)]) over (Intersect([Name of bank],Previous([Period_String])))) /Sum([iA.01 Zins- und Diskontertrag (1.1)]) over (Intersect([Name of bank],Previous([Period_String])))Now I wish to extend this custom expression to calculate the growth rate between periods with a difference of 12 months and larger (e.g. between 2017-12 and 2016-12).

How can I do this

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...