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.YearNew_Dev_Time_TotalLaunch_New_Program_CountStrategy NameProgram NameProduct CategoryCalcOutput Note20227.6273971771CCA7.6273971777.627397177 20227.1013697851DDB7.1013697857.101369785 2023 null7.36average ( all values in 2022)2024 null7.36

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

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

Developer

Transformation

Accepted answers

OllieClarke

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

non-null years.yxmd

All comments

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.

annual_avg.yxmd

OllieClarke

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

non-null years.yxmd

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.

New Workflow14.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703