YTD Month on Month growth computation

[rakeshpaul]

Hello All,

I am trying to calculate the sales cumulative growth, lets say I want to see the YTD month on month sales growth. Lets me be more elaborate -

Lets say I am on April 2015, so when I will calculate the growth it word be nothing but the ((April Sales 2015/April Sales 2014)-1)% and when I am looking on August 2015 Sales growth then formula would be (((April sales 2015+May sales 2015+June sales 2015+July sales 2015+August sales 2015)/(April sales 2014+May sales 2014+June sales 2014+July sales 2014+August sales 2014))-1)%

I am attaching the data and desired the output also-

Month	2014	2015	Cumulative 2014	Cumulative 2015	Growth
Apr	11,355,827.30	11,688,010.89	11,355,827.30	11,688,010.89	2.93%
May	12,332,445.78	14,450,906.30	23,688,273.08	26,138,917.19	10.35%
Jun	13,994,001.11	14,773,599.56	37,682,274.19	40,912,516.75	8.57%
Jul	13,714,112.69	13,975,539.26	51,396,386.88	54,888,056.01	6.79%
Aug	14,492,648.48	7,703,767.47	65,889,035.36	62,591,823.48	-5.00%
Sep	7,632,931.03	14,180,434.15	73,521,966.39	76,772,257.63	4.42%
Oct	12,430,265.12	12,724,260.52	85,952,231.51	89,496,518.15	4.12%
Nov	13,242,570.18	6,719,885.34	99,194,801.69	96,216,403.49	-3.00%
Dec	15,322,086.06	14,666,806.41	114,516,887.75	110,883,209.90	-3.17%
Jan	9,854,753.18	16,194,947.55	124,371,640.93	127,078,157.45	2.18%
Feb	14,201,979.18	14,827,498.76	138,573,620.11	141,905,656.21	2.40%
Mar	13,768,417.32	12,862,753.16	152,342,037.43	154,768,409.37	1.59%

I used the foll wing formula to calculate the Cumulative 2014 & Cumulative 2015 Sales and it's coming correctly-

Cumulative_2015: Sum({<Year={"$(V_Curr_Year)"}>}Sales), where V_Curr_Year = Num(right(GetCurrentSelections(Year),4))

Cumulative_2014: Sum({<Year={"$(V_last_Year)"}>}Sales), where V_Curr_Year = Num(right(GetCurrentSelections(Year),4))-1

And after that I tried to use these following formula to calculate the growth-

((Cumulative_2015/Cumulative_2014)-1)%

But though Cumulative value was correct I not getting the Growth properly. Please let me know where I am doing wrong and how I can fix this issue.

Regards,

Rakesh Paul

[HirisH_V7]

Hi,

Please post your sample data  in excel sheet,

Date and Sales columns

Hirish

[HirisH_V7]

Hi,

Check this ,

using your data,

Data:
LOAD * INLINE [
    Month, Sales, Year
    Apr, "11,355,827.30", 2014
    May, "12,332,445.78", 2014
    Jun, "13,994,001.11", 2014
    Jul, "13,714,112.69", 2014
    Aug, "14,492,648.48", 2014
    Sep, "  7,632,931.03", 2014
    Oct, "12,430,265.12", 2014
    Nov, "13,242,570.18", 2014
    Dec, "15,322,086.06", 2014
    Jan, "  9,854,753.18", 2014
    Feb, "14,201,979.18", 2014
    Mar, "13,768,417.32", 2014
    Apr, "11,688,010.89", 2015
    May, "14,450,906.30", 2015
    Jun, "14,773,599.56", 2015
    Jul, "13,975,539.26", 2015
    Aug, "  7,703,767.47", 2015
    Sep, "14,180,434.15", 2015
    Oct, "12,724,260.52", 2015
    Nov, "  6,719,885.34", 2015
    Dec, "14,666,806.41", 2015
    Jan, "16,194,947.55", 2015
    Feb, "14,827,498.76", 2015
    Mar, "12,862,753.16", 2015
];

Front end:

Here i have created a straight table,

Expressions:

For 2014      =Sum({<Year={"$(=Max(Year)-1)"}>}Sales)
For 2015      =Sum({<Year={"$(=Max(Year))"}>}Sales)  
For 2014   Accumulation  =Rangesum(Sum({<Year={"$(=Max(Year)-1)"}>}Sales),Above(Cummulative_2014))
For 2015   Accumulation  =Rangesum(Sum({<Year={"$(=Max(Year))"}>}Sales),Above(Cummulative_2015))

Growth Rate  =((Column(4)/Column(3))-1)  For this expression in number format fixed to 2 decimal Show as percent.

HTH,

PFA,

Hirish

[rakeshpaul]

Hi Harish,

Thanks for your Response.

It's not the desired output which I was lookign for. And moreover the Growth percentage is not calculated properly.

Regards,

Rakesh Paul

[HirisH_V7]

Hi,

Please check the above changed post of mine.

Hope that helps,

Hirish