Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Help! Fill in blanks column by averaging left and right values

AlteryxUser32
5 - Atom

Hi All,

 

New to the community!

 

Hoping I could get some help on the below problem. 

 

I'm trying to fill in the blank cells in the below illustrative excel. 

 

I want to fill in the cells using a weighted average of the "before" and "after" monthly data. 

 

E.g., For Item 1 -

Month 3 = 50% * Month 2 + 50% * Month 5

Month 4 = 50% * month 3 + 50% * Month 5

 

Similar for Item 2, etc. except there are different "blank" time periods for each item. 

3 REPLIES 3
atcodedog05
22 - Nova
22 - Nova

Hi @AlteryxUser32 

 

Here is how you can do it. Since the next value is not always in the next row. We need to down to up fill.

Workflow:

atcodedog05_1-1628327661482.png

 

1. Using record id to set row id. Unique key for row.

2. Using transpose tool to convert months columns to rows.

3. Sorting the months on descending. Since only top to down fill is possible we need to reverse order to mimic down to top.

4. Using multi-row formula tool doing next value fill.

5. Join back using join tool.

6. Using multi-row formula to calculate if blank avg of prev value and next value.

7. Using crosstab to bring it back to table.

8. Using dynamic rename to fix the names.

 

If this not the expected output please provide the expected output so that we can work towards it.

 

Hope this helps : )

danilang
19 - Altair
19 - Altair

Hi @AlteryxUser32 

 

A clever solution from @atcodedog05.  It does indeed allocation the  difference in a geometric series as you stated.  1/2 the difference is allocated to the first null, 1/4 to the second null, 1/8 to the third, etc.  If you want a different distribution, you need to take a different approach.  

danilang_0-1628336742464.png

This workflow calculates the number of missing values and the range to be allocated between them.  The final Multi-Row tool is where you can determine how you'd like them allocated.  The data going into this tool contains

1) the total amount to be distributed to each missing value,

2) the number of missing values in this gap

3) the ordinal value of each missing value, 1st, 2nd, 3rd, etc

 

Using these 3 values you can distribute the missing values in almost any way imaginable.  I've done it linearly, so the missing values are evenly distributed. 

danilang_1-1628337621084.png

 

To get the geometric distribution as in @atcodedog05's solution, replace the final formula with

if isnull([Value]) then
   [Row-1:NewValue]+[BracketingDiff]/(pow(2,[ConsecutiveBlanks]))
else
   [Value]
Endif

 

Dan 

 

 

AlteryxUser32
5 - Atom

Thank you both! Really amazing solutions.

Labels
Top Solution Authors