Linest function to create a trend for the rest of the year?

[mikegrattan]

I just started experimenting with the Linest functions and found an example in the community on which I based my first attempt.  My goal is to get a projected trend for the rest of the current year, based on data that already exists for the current year to date and two prior full years.  The expression that I am currently experimenting with uses a Month-Year but I will replace it with Week-Year at some point.

linest_m(total aggr(if(Sum(ShippedQuantity),sum(ShippedQuantity)),MonthYear),MonthYear)*

only({1}MonthYear)+linest_b(total aggr(if(sum(ShippedQuantity),Sum(ShippedQuantity)),MonthYear),MonthYear)

Here's how the resulting chart looks (note that filtering on a particular year doesn't exactly work since the expression uses  {1} to utilize the full data set in the application).  Also, as an aside, note that my MonthYear member isn't formatting as expected; I'm loading it in my data load script as:  Date(monthstart(ShipDate), 'MMM-YY') as MonthYear.

All Years

[sunny_talwar]

So the dip is what is the problem right? Would you be able to share a sample where you are trying to do this?

[mikegrattan]

Hi Sunny,

No, the dip is not the problem; that's just indicating a drop in volume due to an incomplete week/month.  The problem is that I need the red Trend line to forecast the rest of the year, so the blue line would stop in March because that shows actual shipments and the red line would forecast out the rest of months in the year, based on prior year data (we have three years data in this application).

I don't know if Linest is the best way to forecast out for future months, so I posted what I've come up with so far in order to either fine-tune it or get other ideas.

Thanks!

[sunny_talwar]

Linest is the way to go, but do you have future MonthYear in your dashboard? It can only show future MonthYear if the values for future MonthYear are available in your dashboard.

[mikegrattan]

That makes sense but no, I do not have the future MonthYear in the application.  Would I just concatenate some empty records in the data load script to get those?

[sunny_talwar]

---

@mikegrattan wrote:

That makes sense but no, I do not have the future MonthYear in the application.  Would I just concatenate some empty records in the data load script to get those?

---

Yup 🙂

[mikegrattan]

Ok, I can work on doing that. 

On my other issue with the MonthYear format; I'm loading the MonthYear in the script, using 

Date(monthstart(ShipDate), 'MMM-YY') as MonthYear

but, it shows up like this:  '1-1-17', instead of Jan-17.  I also tried using the Floor function: Date(monthstart(Floor(ShipDate)), 'MMM-YY') as MonthYear

but that didn't work either.  Any ideas on how to get it to format correctly?

[sunny_talwar]

I think this might be something you would want to fix in the chart. I am not sure how, but there should be a way to change the display on MonthYear field. Script looks okay to me

[mikegrattan]

There's a check-box for "Use continuous scale" in the X-axis Appearance settings that is checked by default.  I unchecked it and it is now displaying correctly.

I added the following to the script and I'm now getting a better trend line - Thanks 

Concatenate (SalesWithCostingManual)
LOAD "CL$JYD" as ShipDate,
"CL$PW" as ShipPoolWeek,
Date(monthstart(Floor("CL$JYD")), 'MMM-YY') as MonthYear;
SQL SELECT "CL$JYD",
"CL$PW"
FROM B10D0B2R.SCRESOURCE."F00@07"
WHERE Year("CL$JYD") = Year(Now())
AND Month("CL$JYD") > Month(Now());

If you don't mind, do you think you could help me scale the Linest function so it works at the Commodity level and have the chart be able to have a filter for the Year? I'm presuming that I can replace the {1} with {$} so the chart can be filtered by Year, but when I try to do so, it won't show the future MonthYear trend line; that only seems to work with no filters.

For the Commodity to be included in Linest, I'm guessing I would edit the expression to include Set analysis but I haven't seen any examples of that in the Community and I'm not sure exactly how to go about it.  I'm thinking that I would possible change Sum(ShippedQuantity) to Sum({$<Commodity=>} Shipped Quantity) but I'm not sure if that's all that would be required.

Thanks.

Edit with screen shot - shows Trend number is not scaled down to Commodity.  Updated Linest expression as follows:

linest_m(total aggr(if(Sum({$<CommodityCode=>} ShippedQuantity),sum({$<CommodityCode=>}ShippedQuantity)),MonthYear),MonthYear)*

only({1}MonthYear)+linest_b(total aggr(if(sum({$<CommodityCode=>}ShippedQuantity),Sum({$<CommodityCode=>}ShippedQuantity)),MonthYear),MonthYear)