Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Thorqqque
Contributor
Contributor

How to stop a chart trendline at the current date

I have a trend line that just keeps on going after the current date.  It would probably be OK if it were a straight line, but I've checked the Polynomial of 4th Degree checkbox in the Trend Line section on the Expressions tab of the chart's Properties dialog, so I can show how orders tend to rise and fall with the shopping seasons of the year.  On the same chart, I have a line each for a couple of past years, and they look great (Red line on chart, below.)  Unfortunately, for the current year, the line looks good up to the current date, then quickly drops off to zero for the rest of the current year, and makes it look like we're projecting zero orders and business is getting bad (Blue line on chart, below.)

Past Year good red line; Current Year bad blue line.Past Year good red line; Current Year bad blue line.

 

 

 

 

 

 

Here's my expression I use for the current year:

SUM ( IF( Fiscal_Year = YEAR( Now() ), Retail_Price, 0))

I thought if I put null() instead of zero as the ELSE it might solve the problem,

SUM ( IF( Fiscal_Year = YEAR( Now() ), Retail_Price, Null()))

But I see no difference between the two, on the chart.

Is there a way, in QlikView, to stop a curve/trend line from continuing after the current date?

Thanks in advance.

Labels (3)
2 Replies
zhadrakas
Specialist II
Specialist II

Hello,

you cannot limit the appearance of the trendline.

What you can do is create your own expression for the trendline and limit that to your dataset.

Sample for Linear Trend:

if( DATE < date(today()),
	linest_m(total aggr(if(sum(Retail_Price) ,sum(Retail_Price)), YOUR_DIMENSION), YOUR_DIMENSION)
	* only(DATUM) + linest_b(total aggr(if(sum(Retail_Price) ,sum(Retail_Price)), YOUR_DIMENSION), YOUR_DIMENSION)
)

 

Brett_Bleess
Support (Former)
Support (Former)

Did the post help you with your use case?  If so, do not forget to return to the thread and use the Accept as Solution button on that post to give the poster credit for the help and let other Community Members know things worked.  If you still need further help, please leave an update.

You can always check the Design Blog area for posts there that may give you some further ideas too:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

That is the base URL from where you can search just that area to try to find posts that might be helpful.  Not sure there is anything on this particular topic, but keep that handy for the future.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.