Growth % on a line chart

[Haarika]

Hi All,

I am trying to plot YTD/MAT/Rolling 3 month growth %'s on a line chart using the below expressions.

YTD - RangeSum(Above(Sum(Actual), 0, MonthNum))/Above(RangeSum(Above(Sum(Actual), 0, MonthNum)), 12) - 1

MAT - (RangeSum(Above(Sum(Actual), 0, 12))/Above(RangeSum(Above(Sum(Actual), 0, 12)), 12) - 1)

Rolling 3M - (RangeSum(Above(Sum(Actual), 0, 3))/Above(RangeSum(Above(Sum(Actual), 0, 3)), 12) - 1)

By using these expressions, the growth % values for the first few months is getting skewed up as shown below.

Could you please suggest how the values can be accurately plotted to avoid any discrepancy.

@sunny_talwar - kindly provide your expert advise 🙂

[ramchalla]

@Haarika , please refer the below link. it would be helpful for you to create rolling sum/average.

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130 

[Kushal_Chawda]

@Haarika  would you be able to share the sample data with expected output?

[Kushal_Chawda · Accepted solution]

@Haarika  meanwhile you can try with sortable aggr option. Where MonthYear is your dimension of the the chart. Make sure that MonthYear is Numeric format i.e either formatted by Date or monthname function

YTD - =sum(aggr(RangeSum(Above(Sum(Actual), 0, MonthNum))/Above(RangeSum(Above(Sum(Actual), 0, MonthNum)), 12) - 1,
(MonthYear,(NUMERIC,ASCENDING)),MonthNum))  // or try removing MonthNum from aggr

MAT - sum(aggr(RangeSum(Above(Sum(Actual), 0, 12))/Above(RangeSum(Above(Sum(Actual), 0, 12)), 12) - 1),
(MonthYear,(NUMERIC,ASCENDING))))

Rolling 3M - sum(aggr(RangeSum(Above(Sum(Actual), 0, 3))/Above(RangeSum(Above(Sum(Actual), 0, 3)), 12) - 1),
(MonthYear,(NUMERIC,ASCENDING))))

[Haarika]

Hi @Kushal_Chawda , attached sample dataset for your reference. I will try out your solution and let u know. thank you 🙂

[Kushal_Chawda]

@Haarika  do you want all months in chart from 2018?

[Haarika]

@Kushal_Chawda - The expressions provided by you work perfect 🙂  Thank you providing a great solution 🙂 For now we can mark this problem as solved. I will reach out to you if I encounter any further issues.

Thank you once again 🙂

[Kushal_Chawda]

@Haarika  No problem