Take the value and average from previous year if current value is null

[zzhangs]

Hi Community,

quick question on Alteryx:

I am trying to get an average value from previous year if the current row is null, the values from previous year is break out into row level. 

I have attached the output here for reference. 

I am having some struggles to figure this out, thanks for the help in advance.

Year	New_Dev_Time_Total	Launch_New_Program_Count	Strategy Name	Program Name	Product Category	Calc	Output	Note
2022	7.627397177	1	C	C	A	7.627397177	7.627397177
2022	7.101369785	1	D	D	B	7.101369785	7.101369785
2023						null	7.36	average ( all values in 2022)
2024						null	7.36

[AGilbert]

Use the multi-row tool to update the Calc field with "if isnull([calc]) then [row-1:calc] else [calc]". This assumes that your Year field is sorted in ascending order. 

[AGilbert]

Ah, I see the complexity of multiple rows of 2022.

I would use a Summarize with group by year and average year. You can join the annual averages to each row of the original table (creating an avg_calc field). You can then use two multi-row tools to fill the null values. See the attached workflow. 

[OllieClarke · Accepted solution]

@zzhangs here's another way which will update all nulls with the average of the most recent non-null year

[Hsandness]

You could use 3 multi-row tools. One to count the amount of records in each year, one to sum each output amount for each year and a third multi-row tool that will get the average of the previous year(s) if the current value in the calc column is "null". The years would have to be the first column sorted, doesn't matter if they're ascending or descending though.