Always show the intersection of two trendlines

[#1]

Hi everyone, I currently have have a combo chart with positive and negative values for bar charts and two linear trend lines as pictured in my attachment. I have enabled forecasting and the x-axis is continuous. This data will vary throughout different sessions, and I was wondering if it was possible to make it so that the graph always shows where the two trend lines intersect. In this session, it looks like the trend lines intersect on 2/6/2015 for instance.

This is what I'm using for the forecast expression:

((LINEST_B(Total Aggr(Sum([B1]), [Date]),[Date]) - LINEST_B(TOTAL Aggr(Sum([B2]), [Date]), [Date]))/(LINEST_M(TOTAL Aggr(Sum([M2]), [Date]), [Date]) - LINEST_M(Total Aggr(Sum([M1]), [Date]),[Date])))

I'm basing this off something like this: Intersection of Two Lines | Zona Land Education

If there's anything wrong with my equation then please let me know, as I'm still relatively new to QlikView.

My reasoning is that, if I can just find how far out along the x axis I need to go then I should be able to always show where the two lines intersect, hence I only solve for where they intersect along the x-axis, but for some reason the forecast expression goes too far out. I think this might be because the dates are calculated in their respective number format.

Does anyone have any suggestions on how to approach this? (I'm currently using QlikView personal edition, so if it's possible I would prefer to get help from within the comments and not the .qvw files)

[sudeepkm]

I think it would be better to calculate these at the script level. You can create a table that would capture the aggregated result and the respective date where the lines would collide. Since this is kind of predicting so it may happen you wont have the necessary date values present in your current data set so you can use auto generate to produce the future dates and can show the value in your front end.

[#3]

Could you explain why calculating the expression at the script level would be better? I'm just trying to calculate what the forecast should be so that the graph always extends to a point beyond the interception point of the two lines.