Good Afternoon Alteryx Community,
I have a multi-step problem that I will try to explain as clear as I can since the formula I have been trying with if clauses is not working. I have attached my workspace so you can see what I am trying to achieve (focus on bottom workflow - the top is to show you why I have to do this). Excel has a graph function to connect data points with lines and ignore the null values unforuntately alteryx does not to my knowledge.
First Formula Goal: if the postop 1-3mo is empty then fill it with the next follow-up data value
Second Formula Goal: as you can see from the top workflow - the chart needs values filled in to properly look right. I tried to achieve this the second formula but I clearly have failed since its not averaging and its skipping some cells. So it might need a full rework.
I need it to look at the last follow-up and average it with the next follow-up and fill it into the adjacent cell and continue to do this so it fills in a value for every postop period between the final and initial postop 1-3mo followup:
To better show you, I took part of the data and did it manually with a before and after in excel.
Raw Data:
| Last | First | Eye | Preop IOL Ref Sph | Postop 1-3mo Sph | > 4-12mo Sph | > 1-2yrs Sph | > 2-3yrs Sph | > 3-5yrs Sph | > 5-7yrs Sph | > 7-9yrs Sph | > 10yrs Sph |
| 1 | 1 | OD | -9.50 | 0.00 | | | | | 1 | | |
| 2 | 2 | OD | -15.50 | 0.75 | | 0.50 | | | | | |
| 3 | 3 | OS | -15.50 | 0.50 | | 0.25 | | | | | |
| 4 | 4 | OD | -20.25 | | | | | 0.5 | | | 1 |
| 5 | 5 | OD | -15.00 | | | | 1 | | | | |
| 6 | 6 | OD | -7.00 | | | 0.50 | | | 1 | | |
| 7 | 7 | OS | -5.00 | | | 0.50 | | | 1 | | |
And here is what I would like alteryx to be able to do and where my formula is failing:
| Last | First | Eye | Preop IOL Ref Sph | Postop 1-3mo Sph | > 4-12mo Sph | > 1-2yrs Sph | > 2-3yrs Sph | > 3-5yrs Sph | > 5-7yrs Sph | > 7-9yrs Sph | > 10yrs Sph |
| 1 | 1 | OD | -9.50 | 0.00 | 0.50 | 0.50 | 0.5 | 0.5 | 1 | | |
| 2 | 2 | OD | -15.50 | 0.75 | 0.63 | 0.50 | | | | | |
| 3 | 3 | OS | -15.50 | 0.50 | 0.38 | 0.25 | | | | | |
| 4 | 4 | OD | -20.25 | 0.50 | 0.50 | 0.50 | 0.5 | 0.5 | 0.75 | 0.75 | 1 |
| 5 | 5 | OD | -15.00 | 1.00 | 1.00 | 1.00 | 1 | | | | |
| 6 | 6 | OD | -7.00 | 0.50 | 0.50 | 0.50 | 0.75 | 0.75 | 1 | | |
| 7 | 7 | OS | -5.00 | 0.50 | 0.50 | 0.50 | 0.75 | 0.75 | 1 | | |
I would greatly appreciate if you attach a workspace since these formulas get quite confusing and I am sure my formula has to be completely redone since its not doing exactly what I want right now. Thank you in advance and if you have any questions please feel free to ask.
Nick
