1

I have a somewhat similar question to: MySQL cumulative product group by but I'm unable to get the proposed solution to work for my situation.

I have 5 month over month percentage change values that I want to apply to a static value. I want the resulting value to be cumulative for all months.

Report_Month PCT_Change Initial_Value
8/1/2021 0.1202 0.88
9/1/2021 -.0426
10/1/2021 -0.0794
11/1/2021 0.0726
12/1/2021 0.1182

If I were calculating this in Excel the out put would look like:

Report_Month PCT_Change Value
8/1/2021 0.1202 0.985776
9/1/2021 -.0426 0.943781942
10/1/2021 -0.0794 0.868845656
11/1/2021 0.0726 0.931923851
12/1/2021 0.1182 1.04207725

However, when I use the exp(sum(ln(x))) method that's not what I'm getting. This is the output using exp(sum(ln(0.88*(1+pct_change))) over (order by report_month)).

Report_Month PCT_Change Value
8/1/2021 0.1202 0.9857
9/1/2021 -.0426 0.8305
10/1/2021 -0.0794 0.6728
11/1/2021 0.0726 0.6351
12/1/2021 0.1182 0.6249

Any assistance would be greatly appreciated.

Thanks, Chris

forpas
  • 160,666
  • 10
  • 38
  • 76
Chris
  • 105
  • 1
  • 1
  • 4

1 Answers1

3

The value of Initial_Value must be multiplied to the result of the SUM() window function, so the correct expression is:

0.88 * EXP(SUM(LN((1 + pct_change))) OVER (ORDER BY report_month))

See the demo.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • 1
    Perfect! This is why I love the Stack Overflow community. Stupid simple mistake, but I've been banging my head against the wall trying to figure it out for hours. Thanks @forpas! – Chris Aug 16 '21 at 13:04