How to stop a chart trendline at the current date

[Thorqqque]

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.

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.

[zhadrakas]

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]

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