2 Replies Latest reply on Jun 27, 2018 8:51 AM by Wesley Magee

# Calculate the difference between this year and last year without a table calculation or parameter

I am trying to develop some dynamic text so that as data gets uploaded the text will automatically update.  I have been successful at creating this function for information about the current year.  But what I want to do is add a sentence that says something like: The number of widgets produced last year was 1,000 and this year was 1,500 and the difference is 500.  I would like to do this without a table calculation or with input from a parameter as I would like the data to update automatically.  The table I am using is simple, and looks like the below.  Thoughts?

YearIndicatorValue
2018Number of widgets1500
2017Number of widgets1000
2016Number of widgets500
• ###### 1. Re: Calculate the difference between this year and last year without a table calculation or parameter

Hi Greg,

Something like this.?

Thanks,

Shin

• ###### 2. Re: Calculate the difference between this year and last year without a table calculation or parameter

Greg,

You could approach it with a series of if/thens to arrive at each number. Something like the following:

Current Year

IF YEAR({MAX(Date)}) = Date THEN [Value] END

Prior Year

IF DATEDIFF('year', YEAR(Date), YEAR(({MAX(Date)})) = 1 THEN [Value] END

Difference

SUM(Current Year) - SUM(Prior Year)

The number of widgets produced last year was [Prior Year] and this year was [Current Year] and the difference is [Difference]

This all assumes you want to use the max year in your data as the current year. Let me know if this isn't what you're looking for.

-Wesley

If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.