topic Re: Add Fixed Trend Line to Combo Chart with 2 Dimensions in QlikView

Add Fixed Trend Line to Combo Chart with 2 Dimensions

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have the following Combo Chart:

With Dimensions: Grades and Customers

And Expression: SUM(Sales_RND)

What I need is to add the Lineal Trend where Y = m X + B like this (but not automatically):

I'd need to add it as a new expression... I already have the values for M and B, calculated like this:

- M = LINEST_M({1}TOTAL AGGR(SUM({1}Sales_RND),GRADE,Customer),GRADE,Customer)

- B = LINEST_B({1}TOTAL AGGR(SUM({1}Sales_RND),GRADE,Customer),GRADE,Customer)

I've seen somewhere that I'd have to add a new expression to get the Lineal Trend with something like this:

LINEST_M({1}TOTAL AGGR(SUM({1}Sales_RND),GRADE,Customer),GRADE,Customer)

*GRADE

+LINEST_B({1}TOTAL AGGR(SUM({1}Sales_RND),GRADE,Customer),GRADE,Customer)

The thing is it's not working, I don't know if it's because I have to multiply the LINEST_M with my 2 Dimensions? (GRADE and Customers). How could I do that in order to work and avoid using 2 charts overlapped? I attach the QVW.

Thanks!!!

Re: Add Fixed Trend Line to Combo Chart with 2 Dimensions

Anil_Babu_Samineni — Fri, 14 Jul 2017 02:20:38 GMT

Can you enable the option Linear from section from Trendlines.

Re: Add Fixed Trend Line to Combo Chart with 2 Dimensions

microwin88x — Fri, 14 Jul 2017 03:04:13 GMT

Hi,

No because I need to create a customized Trend Line, not the one by default...

Re: Add Fixed Trend Line to Combo Chart with 2 Dimensions

Anil_Babu_Samineni — Fri, 14 Jul 2017 03:19:27 GMT

Okay, But when i enable second expression looks like i got three trend curves. But, You are expecting one curve only. What does the one curve indicates when we use custom formula like you mentioned?

Re: Add Fixed Trend Line to Combo Chart with 2 Dimensions

microwin88x — Fri, 14 Jul 2017 03:28:37 GMT

That curve would be the general Trend Line for all the dots, it aggregates the sum from sales for every grade and customer and it has to be a fixed value, that's why you see the {1}.

I'd be satisfied for now if I could only draw the line as an expression for now (and then I would change the line color, let it fixed ignoring selections, or using show conditional function).