Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

How do I write a formula with a dynamic time reference?

This article includes suggestions and workarounds. Content may not be accurate for all use cases or represent best practices for the latest release. 

Question

I need to write a formula that functions like this: ACCT.IT_Expense[time=01/2017] except instead of 2017, I 'd like to reference January of which ever year being reported on. How can I write the time modifier to function this way?

Answer

 With current functionality, there is not a syntax to dynamically reference the first month of the current year. For example, you cannot write a formula that says, ACCT.IT_Expense[time=1/this.year]. However, you can get similar results using an if statement. Your formula would look something like:

iff(year(this)=2016,ACCT.IT_Expense[time=01/2016],iff(year(this)=2017,ACCT.IT_Expense[time=01/2017],iff(year(this)=2018,ACCT.IT_Expense[time=01/2018]...