4 Replies Latest reply on Mar 15, 2016 11:39 AM by pooja.gandhi

# LOD Calculation: Annual Growth

The following is what I want to achieve:

Annual Growth = Price on last day of year / Price on first day of year - 1

**Note**: First day and last day of year: the earliest or latest day available in the data, doesn't mean fixed 1/1 or 12/31.

Attached a sample workbook.

How to write a LOD cal to return the price for a specific day, for example, give me the price of earliest day on each year.

The quick table calculation "percent difference" doesn't work on this case.

**********************Sample Data**************************************

Date               Price

1/1/2010           5

1/2/2010           7

12/31/2010      10

1/2/2011            3

1/3/2011            5

12/30/2011        7

6/7/2012            6

10/30/2012        8

2010:   10/5-1=1

2011:   7/3-1=1.33

2012:   8/6-1=0.33

Joe Oppelt

Joshua Cloud

Let me know if you can help.

• ###### 1. Re: LOD Calculation: Annual Growth

Hi Jerry!

That would be:

First Day of Year: if [Date] = { FIXED [Year]:MIN([Date])} then [Price] end

Last Day of Year: if [Date] = { FIXED [Year]:max([Date])} then [Price] end

Division: (sum([Last Day of Year])/sum([First Day of Year])) - 1

1 of 1 people found this helpful
• ###### 2. Re: LOD Calculation: Annual Growth

Wish I could help.  I'm tied up today.

• ###### 3. Re: LOD Calculation: Annual Growth

Hi Pooja,

Thanks again for quick solving my problem.

What if I change the formula of annual growth to:

Last day of year / Last day of previous year - 1  (if last day of previous year is not available, then use the first day of current year)

In this sample data, it should be:

2010: 10/5-1 =1

2011: 7/10-1 = -0.3

2012: 8/7-1 = 0.14

• ###### 4. Re: LOD Calculation: Annual Growth

That would be first calculating the last day of the previous year. You can do that with table calcs:

Previous Year: IFNULL(lookup(sum([Last Day of Year]),-1), sum([First Day of Year]))

New Division: (sum([Last Day of Year])/[Previous Year])-1

1 of 1 people found this helpful