Forecasting with LAG() - NULL issue

Copper Contributor

 

 

Some Value     Some Change     Other Value

-------------     ---------------     --------------

x                       a%                      41000

y                       b%                      40750

z                       c%                       NULL

m                     d%                       NULL

k                      f%                        NULL

 

I have data as shown. When the Other Value is NULL, I want to take the LAG value for Other Value and multiple it by the current rows Some Change Column. When I use LAG, it only populates the first NULL. When I get to the next row, LAG returns NULL but I want it to return what was previously calculated multiplied by the next percent in the Some Change column.

 

For example:

1) 40750 x c%  (This works with LAG but the rest do not)

2) (40750 x c%) x d%

3) ((40750 x c%) x d%) x f%

 

Is there a way to do this inline without populating a table and looping over the NULLs?

 

Sample code to work with:

 

DROP TABLE IF EXISTS #LAGISSUE;

CREATE TABLE #LAGISSUE(
	ROWID INT,
	SOMEVALUE INT,
	PercentChange FLOAT,
	OtherValue int
)


INSERT INTO #LAGISSUE
SELECT 1,40000, .98254, 41000;

INSERT INTO #LAGISSUE
SELECT 2,41000, .9654, 40750;

INSERT INTO #LAGISSUE
SELECT 3, 42000, .92012,  NULL;

INSERT INTO #LAGISSUE
SELECT 4, 43000, .9357, NULL;

INSERT INTO #LAGISSUE
SELECT 5, 43000, .8976, NULL;


SELECT SomeValue, PercentChange, OtherValue,
	CASE WHEN OtherValue IS NULL THEN

		ROUND(LAG(OtherValue) OVER(ORDER BY ROWID) * PercentChange,0)

	END AS ForecastedValue
FROM #LAGISSUE;


DROP TABLE IF EXISTS #LAGISSUE;

 

SQL gives the following results. How can I get row 4 to be 37495 * .9357 and so on?

HSBHSB_0-1713188027426.png

 

 

0 Replies