Bar Chart for Trend Line Slope

[Karin]

Hi All,

I am quite new to Qlik Sense. I need to create a bar chart with bars being slopes of the trend line for sales of the last 12months, rolling.

In Excel, slope is calculated by the formula SLOPE(known_y’s, known_x’s)

The graph would look like this:

Bar “Jan-20” shows the slope (measure) of the trend line for sales for the period Feb19-Jan20.

Bar “Dec-19” shows the slope of the trend line for sales for the period Jan19-Dec19, etc.

I know how to get the trendline for 12 months in Qlik Sense. In my case it is the following expression:

linest_m(total aggr(if(Sum({<is12M={1}, AsOfMonth={'$(=vMaxMonthNo)'}>}[Sales]),Sum({<is12M={1}, AsOfMonth={'$(=vMaxMonthNo)'}>}[Sales])),MonthYear),MonthYear)*only(MonthYear)+linest_b(total aggr(if(Sum({<is12M={1}, AsOfMonth={'$(=vMaxMonthNo)'}>}[Sales]),Sum({<is12M={1}, AsOfMonth={'$(=vMaxMonthNo)'}>}[Sales])),MonthYear),MonthYear))

To my understanding, the bold part is the slope. But how to calculated the slopes for rolling 12 months and put them all in one bar chart?

Any help on this is highly appreciated!

[Karin]

Yes -I also think we are almost there!

Unfortunately I can't share the dashboard.

I did as you suggested - changed the Dimension to AsOfMonthYear. See what happens:

As Measure I have this:

LinEst_M( aggr(sum({<is12M={1},AsOfMonth={">=12<=24"} >}[Sales]), MonthYear, AsOfMonthYear), aggr(Only({<is12M={1},AsOfMonth={">=12<=24"}>}MonthYear), MonthYear, AsOfMonthYear))

As I wrote above, replacing  MonthYear with AsOfMonth is not working.

Thanks!

[lorenzoconforti]

can you share the excel file you used to produce the chart with the different scale?

[Karin]

Yes-sure. I attach the Excel file with the dummy data and slope calculation.

[lorenzoconforti · Accepted solution]

Try this please:

LinEst_M( aggr(sum({<is12M={1},AsOfMonth={">=12<=24"} >}[Sales]), MonthNo, AsOfMonthYear), aggr(Only({<is12M={1},AsOfMonth={">=12<=24"}>}MonthNo), MonthNo, AsOfMonthYear))

[Karin · Accepted solution]

YES!!! It worked!!  Amazing. Thank you so much!!! I highly appreciate your help!

Just to summarize the solution:

Dimension expression:

 =aggr(ONLY({<MonthNo={'13','14','15','16','17','18','19','20','21','22','23','24'}>}AsOfMonthYear),AsOfMonthYear)

Measure expression:

=LinEst_M( aggr(sum({<is12M={1},AsOfMonth={">=12<=24"} >}[Sales]), MonthNo, AsOfMonthYear), aggr(Only({<is12M={1},AsOfMonth={">=12<=24"}>}MonthNo), MonthNo, AsOfMonthYear))

[lorenzoconforti]

Finally! Well done for not giving up