topic Re: How do I add a conditional "Trend line" for a bar chart? in QlikView

How do I add a conditional "Trend line" for a bar chart?

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

I have a bar chart with cyclic group. I want to add "Linear" trend line to bar chart (Expression-> trend line). But This trend line apprears to both cyclic group. I want it only for one. Is that possible?

I have attached an example:

Re: How do I add a conditional "Trend line" for a bar chart?

rupamjyotidas — Tue, 27 Sep 2016 19:36:58 GMT

That will be not possible currently. What I suggest is using a two graphs with a inline table (%Month, MonthYear), in this way you can toggle between the charts and your trend line can be different.

Re: How do I add a conditional "Trend line" for a bar chart?

Anonymous — Tue, 27 Sep 2016 19:57:32 GMT

Without cyclic group, how do I toggle between the graphs?

Re: How do I add a conditional "Trend line" for a bar chart?

sunny_talwar — Tue, 27 Sep 2016 20:46:20 GMT

You can use Linest functions to create the trend line and make it condition to show only when your on certain cycle group

linest_M(total aggr(Sum(%Revenue), MonthYear),MonthYear)*MonthYear + linest_b(total aggr(Sum(%Revenue),MonthYear),MonthYear)

But for this to work, I had to create a MonthYear field in the script. New script:

DateDemo:

LOAD *,

Date(MonthStart(Date#(%Month & '-' & %year, 'MMM-YYYY')), 'MMM YYYY') as MonthYear;

LOAD * INLINE [

%Month, %year, %Revenue

Jan,2015,250000

Feb,2015,895456

Mar,2015,871594

Apr,2015,324791

May,2015,894157

Jun,2015,139714

Jul,2015,794351

Aug,2015,946321

Sep,2015,521493

Oct,2015,879423

Nov,2015,974126

Dec,2015,100357

Jan,2016,250842

Feb,2016,795456

Mar,2016,571594

Apr,2016,724791

May,2016,1094157

Jun,2016,1039714

Jul,2016,994351

Aug,2016,946321

Sep,2016,621493

Oct,2016,227942

Nov,2016,474126

Dec,2016,1100357

];

Now when you use the expression, you can see how it works. When on MonthYear dimension

When on % Month dimension

Hide show condition for the trend line is this

=GetCurrentField([CyclicGroup]) <> '%Month'

Re: How do I add a conditional "Trend line" for a bar chart?

rupamjyotidas — Tue, 27 Sep 2016 20:57:07 GMT

Make a inline table like

TrendLineIsland:

LOAD * INLINE [

TrendlineShow

Original

Accumulated

];

And use this as Selection box instead of cycle group, you can always show/hide graph using layout tab.

But Sunny Solution will work best, if you try it out.

Re: How do I add a conditional "Trend line" for a bar chart?

Anonymous — Tue, 27 Sep 2016 21:35:35 GMT

You are awesome! This is what I wanted to do. Thank you so much. I will try this on my data and let you know.

Re: How do I add a conditional "Trend line" for a bar chart?

Anonymous — Tue, 27 Sep 2016 21:36:05 GMT

Thank you for your help.

Re: How do I add a conditional "Trend line" for a bar chart?

sunny_talwar — Wed, 28 Sep 2016 00:53:49 GMT

Sure thing

Re: How do I add a conditional "Trend line" for a bar chart?

Anonymous — Wed, 28 Sep 2016 15:05:25 GMT

Worked just fine. I do have one question. If I already have a column called MonthYear (In this format: 'YYYY-MM' for example '2016-03'). What will be the "Linest" function or what else we have to change in the script?

Thanks

Pavithra Shetty

Re: How do I add a conditional "Trend line" for a bar chart?

sunny_talwar — Wed, 28 Sep 2016 17:34:38 GMT

If you MonthYear field is created using a date function then you can simply replace my MonthYear field with yours on the front end. Not changes should be needed in the script I believe.

Best,

Sunny

Re: How do I add a conditional "Trend line" for a bar chart?

Anonymous — Thu, 29 Sep 2016 17:11:17 GMT

No. Date field comes from the table. What should I do in this case?

Thanks

Pavithra Shetty

Re: How do I add a conditional "Trend line" for a bar chart?

sunny_talwar — Thu, 29 Sep 2016 17:17:53 GMT

Create MonthYear using MonthName() function

Re: How do I add a conditional "Trend line" for a bar chart?

Anonymous — Thu, 29 Sep 2016 17:26:19 GMT

Okay, Got it. Thank you so much. I appreciate your help.

-Pavithra Shetty