topic Re: trendline on stacked chart in QlikView

trendline on stacked chart

Mon, 22 Dec 2014 19:20:34 GMT

I'm new to Qlikview and I'm wondering if there is a way to accomplish the following. I'd like to add a single trendline (red) to the below mockup where the trendline represents the sum of each data set for the month. For example, in December the first 'Type' column has a value of 16 and the second column is 1,. The trendline value should be 17. I think this may not even be a trendlne but rather a dual chart (bar and line), but I'm not sure how to accomplish. As previouly stated, I'm extremely new at Qlikview, so forgive my ignorance. Thanks.

Re: trendline on stacked chart

ecolomer — Mon, 22 Dec 2014 21:23:27 GMT

Yes it is posible.

You define a new expression, as sum of both, and in the label expression, clik on Line option and leave clik on bar option

Re: trendline on stacked chart

Mon, 22 Dec 2014 21:49:32 GMT

Thank you for the response. I'm not sure I can do that. The Types are there from adding them as a dimension. Technically they are the same data set, just split out. Currently there is a single expression - Count(ID) for the whole chart. Should I maybe set the Types up differently - as two different data sets (maybe need some conditions for each to pull the correct data)?

Re: trendline on stacked chart

ecolomer — Mon, 22 Dec 2014 21:53:03 GMT

Can you upload file qv to see?

Re: trendline on stacked chart

Mon, 22 Dec 2014 22:06:25 GMT

Here is the document.

Re: trendline on stacked chart

Wed, 07 Jan 2015 18:35:18 GMT

Does anyone have any ideas on this? Thank you in advance.

Re: trendline on stacked chart

agomes1971 — Wed, 07 Jan 2015 19:01:48 GMT

Hi,

please see this hint...

Re: trendline on stacked chart

Wed, 07 Jan 2015 20:00:11 GMT

Thank you for the response, Andre. That's close. I modified the formula to read, which gives me the exact line I'm looking for:

sum(TOTAL <ComplaintDate> EscalatedInd)

But, when I switch the chart type to bar/line, the stacked bars go away - it shows only one color for the bar chart. If you have any suggestions on how to preserve the color differentiation on the bar chart that would be great. I'll keep playing with it on my end too.

Re: trendline on stacked chart

r_wroblewski — Thu, 08 Jan 2015 04:07:19 GMT

Hi Brent,

Please check the attached file.

Let me know when something is unclear after checking the file.

Best regards,

Ronny

PS: There are some limitations using a Product LOB listbox due to new expressions. Please check if this solution fits for you.

Re: trendline on stacked chart

Thu, 08 Jan 2015 13:10:53 GMT

Thank you Ronny. That looks like it's pretty close to what Iwas looking for. Unfortunately I'm still using the personal edition so I can't open the file. At some point soon I should be able to obtain a license and I will be able to see how you did it. Thanks for the solution!

Re: trendline on stacked chart

r_wroblewski — Fri, 09 Jan 2015 01:38:44 GMT

Hi Brent,

I changed graph as follows:

Dimensions:

Month

EscalateDesc

Expressions:

- 1 Expressions for 1 ProductLOB (e.g. ProductLOB=Alternative)

Label: Alternative

Definition: Count({$<ProductLOB={'Alternative'}>}HandlingOrgUnit)

DisplayOptions: Bar

create similar Expressions for each ProductLOB, just change wording (marked bold) and Label

- 1 Expression for Total

Definition: Aggr(Count({$<ProductLOB>}HandlingOrgUnit),Month)

DisplayOptions: Line

This line chart ignore your "EscalateDesc" Dimension and count just per Month

Hope this helps you to create same graph now.

Let me know if you need help and would be nice to mark answer as helpful/correct if this solve your issue.

Regards,

Ronny

Re: trendline on stacked chart

Tue, 10 Feb 2015 13:51:38 GMT

Sorry for the delay in response. I didn't see that you posted back with an explanation. I like the solution but unfortunately there are many ProductLOB values and they are dynamic, meaning a new one could be added at some point in the future without me knowing it. Unfortunately I can't hardcode their values into a formula because know the will change. I did try this out and it appeared to work as you specified - a good solution as it stands, but I don't know if it will work in my situation. Thanks for all your help.