Percentage Change Month over Month

[Anonymous]

Please see below for a pivot table I have created. Right now, the redacted cell values are SUM(AMOUNT), however I would like to track the % change of SUM(AMOUNT) in each cell, not the actual SUM of AMOUNT.

Any help is appreciated.

Thank you,

- dave

[sunny_talwar]

Change from last period?

May be this:

Sum(AMOUNT)/Before(Sum(AMOUNT)) - 1

[Anonymous]

Sunny this works immediately thank you. Just a couple of issues:

My data goes all the way back to 2010 but when I use your expression I lose 2010 and the first half of 2011. Any reason you can think of?

Also, is there a way I can express this as 100%. As in - First month of sales = 100% ... If sales increase, 102%, if sales decrease 96%

Thanks again!

[sunny_talwar]

‌for second issue I would say that don't subtract the one and. Change  the number format to percentage on the numbers tab

Sum(AMOUNT)/Before(Sumy(AMOUNT))

[sunny_talwar]

‌regarding the lose of data I am not really sure what moght be causing this. May be there is nothing available for before. May be try this:

Sum(AMOUNT)/Alt(Before(Sum(AMOUNT)), Sum(AMOUNT))

[Anonymous]

This is getting closer but it only shows the percentage change when AMOUNT increases from month to month. I know that is what was initially requested but as I start to do analysis I realize things are changing slightly.

Cohort - A, initial month, Oct 2010, amount $3000 = 100%

Cohort A - Nov 2010 - amount $3500 = 116%

Cohort A - Dec 2010 - amount $4000 = 133%

Cohort A - Jan 2011 - amount $2500 = 83%

Does this make sense? Its almost like I would like to calculate the percentage change every month based on the initial month of the Cohort.

[sunny_talwar]

May be like this:

Sum(AMOUNT)/First(Sum(AMOUNT))

[Anonymous]

That didnt not work unfortunately. Would it help to do some type of aggr with a min statement?

Something like

sum(amount) / min(sum(aggr(Month, Cohort) Sales))

[sunny_talwar]

This seems to be working:

=Sum(AMOUNT)/FirstSortedValue(TOTAL <Cohort> Aggr(Sum(AMOUNT), [Fiscal Period], Cohort), Aggr([Fiscal Period], [Fiscal Period], Cohort))

[Anonymous]

This is still not working for me but I believe I know why.

The AMOUNT is not expressed as you have in your table above. The amount is a SUM of sales for multiple companies that belong to a cohort. An example is provided below: