LinkedIn
Twitter
I am looking for opinions on how to do or improve a “hard coded forecast” (I dont really know what to call this, but I’ll explain).
I have say, a monthly sales figure, or a daily sales figure or whatever (the exact content doesnt matter). In Excel, using pivots and formulae and stuff, we’ve got a “forecast” for each value on the x-axis in a lookup table. This looks something like this:
Now I want to use this in Looker to create a “forecast” line to show the actuals as a bar, and the “forecast” as a line. The underlying table is much more granular - 1 row per account, aggregated to the day/month.
The end result is something like this:
The red line, tracks the actuals up to a point, where it then follows this “forecast”.
Currently, I have this as one HUGE nested if statement with hard coded values in it - which means it is computationally heavy and needs to be manually updated every month from the excel file.
The if statement looks like this:
…
Before you ask, the resulting values are not linear, hence they need to be put in for every day.
Does anyone have a better suggestion of how to do this? or achieve the same effect?
My thoughts are to create an aggregated table in our datawarehouse, with this lookup table feeding in via Google Sheets, and then joining and calculating it there. But that means that I have a separate little table for this tile, which means more maintenance, and it is not explorable.
happy to answer questions if you have - I probably didn’t do a very good job at explaining it.
