Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

rakeshpaul
Not applicable

YTD Month on Month growth computation

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-

    

Month20142015Cumulative 2014Cumulative 2015Growth
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



Tags (2)
4 Replies
hirishv7
Not applicable

Re: YTD Month on Month growth computation

Hi,

Please post your sample data  in excel sheet,

Date and Sales columns

Hirish

“Aspire to Inspire before we Expire!”
hirishv7
Not applicable

Re: YTD Month on Month growth computation

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:

Year - Year Accumulation-203638.PNG

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

“Aspire to Inspire before we Expire!”
rakeshpaul
Not applicable

Re: YTD Month on Month growth computation

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

hirishv7
Not applicable

Re: YTD Month on Month growth computation

Hi,

Please check the above changed post of mine.

Hope that helps,

Hirish

“Aspire to Inspire before we Expire!”