Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂
@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 , 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
@Haarika would you be able to share the sample data with expected output?
@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))))
Hi @Kushal_Chawda , attached sample dataset for your reference. I will try out your solution and let u know. thank you 🙂
@Haarika do you want all months in chart from 2018?
@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 🙂
@Haarika No problem