topic Re: Growth % on a line chart in App Development

Growth % on a line chart

Haarika — Sat, 16 Nov 2024 01:20:23 GMT

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 🙂

Re: Growth % on a line chart

ramchalla — Sun, 04 Oct 2020 11:14:30 GMT

@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

Re: Growth % on a line chart

Kushal_Chawda — Sun, 04 Oct 2020 12:48:10 GMT

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

Re: Growth % on a line chart

Kushal_Chawda — Sun, 04 Oct 2020 12:55:30 GMT

@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))))

Re: Growth % on a line chart

Haarika — Mon, 05 Oct 2020 01:12:14 GMT

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

Re: Growth % on a line chart

Kushal_Chawda — Mon, 05 Oct 2020 09:28:25 GMT

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

Re: Growth % on a line chart

Haarika — Mon, 05 Oct 2020 12:25:58 GMT

@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 🙂

Re: Growth % on a line chart

Kushal_Chawda — Mon, 05 Oct 2020 12:29:44 GMT

@Haarika No problem