Drawing a trendline on partial data

[mhassinger]

I have a combo chart where I show actuals and forecast. The chart spans several years, with actuals as bars starting in the past till present day, and then a line representing forecast going off into the future. In my data, these are basically the same, i.e. a month, year, and number. If it's in the past it's an actual, if it's in the future it's a forecast. I've got a flag in my master calendar which is used to say whether a record is in the future or not.

Overall, everything is working fine, except the trendline. Using the automatic trendline on the expression for actuals doesn't work, because since there's no data for about half the year and beyond, the line just heads down at a steep angle, despite sales being up. So I opted to use another expression and linest_m and linest_b to draw my own line. This works, except the trendline ends where the bars do.

What I've been asked to do is extend that line past the actuals, so that they can see if the trend in actuals is aligning with their forecast numbers. I have not been able to figure out how to make a line that spans the graph, using only the actuals data to compute the starting point and slope. Any ideas?

[swuehl]

Hi,

try maybe

=if(Future=0,SUM({<Future={0}>}Number))

(to avoid the zero values in future).

See also attache.

Stefan

[mhassinger]

Well, that would work, but there's some extra set analysis in the real app that seems to be causing a problem. I've created a new version that more closely resembles what I'm doing. A year value is always selected in the app, and the window of time the chart shows moves based on what year is selected. That's messing with the solution you posted.

Also, I talked to the user, and he would like to see the line formatted a certain way, i.e. a different color than the bars and also dotted. So I think the ideal solution is actually to find a way to extend my green line in this newest example (unless there's a way to format the built-in trendline that I don't know about).

[swuehl]

Well,

you just want to extend your trend line for year 2010 (if selected 2011 , 2012, it looks ok to me)?

Maybe just change the add to the max year in the set expression to +2 like in the calculation for the actuals?

Like

linest_m(

    {$<Year={">$(=max(Year)-3)<=$(=max(Year)+2)"},Future={0}>}

    total

    aggr(if(sum({$<Year={">$(=max(Year)-3)<=$(=max(Year)+2)"},Future={0}>} Number),sum({$<Year={">$(=max(Year)-3)<=$(=max(Year)+2)"},Future={0}>} Number)),MonthYear),MonthYear)

    *only({$<Year={">$(=max(Year)-3)<=$(=max(Year)+2)"},Future={0}>} MonthYear)

+linest_b(

    {$<Year={">$(=max(Year)-3)<=$(=max(Year)+2)"},Future={0}>}

    total

    aggr(if(sum({$<Year={">$(=max(Year)-3)<=$(=max(Year)+2)"},Future={0}>} Number),sum({$<Year={">$(=max(Year)-3)<=$(=max(Year)+2)"},Future={0}>} Number)),MonthYear),MonthYear)

(Or see attached)?

Probably I've missed your point though...

Regards,

Stefan

[mhassinger]

Basically in my second example QVW, if you've got 2011 selected, the green line should go all the way from Jan-2010 through Dec-2012 (so across the entire chart), but the angle and start point of the line should only be determined by the actuals (Future=0) data.

Does that make sense? If the green line never goes to the right of the blue bars, then they can't easily see if the trend in actual sales will cause them to come over or under their forecast. And if the green line extends across the whole chart, it needs to not take that massive dive towards zero.

Any ideas would be great!

[swuehl · Accepted solution]

I think I understood.

Maybe like attached? (I removed Future={0} from parts of the set analysis to allow extending over all MonthYear Values.)

Regards,

Stefan

[mhassinger]

Yep, that did it. Thanks for all the help!

[renem]

So what function would we use to get the Polynomial to the 2nd degree as a value too?

[#9]

It seems that no one answer your question. Just want to get some idea whether it's applicable in QlikView.