Skipping Certain Calculations when Multiple Values are Null

[dishamishra]

Hi there Alteryx Community! 

I have a workflow where I am adding extra columns to the output if certain dates do not exist in the source file. When I try to calculate a ratio using a multi-field row tool, I would like the formula to skip over the null fields. I was wondering if someone could take a look at my formula and let me know what I'm doing wrong? Here are some screenshots:

1) If you take a look at row 33 ... Jan_20 is a column which does not exist in the source file, but I have added it to the workflow so that my final output will have all the years from Jan_18 to Jan_20. I would like this "Current Ratio" calculation to remain null for Jan_20. 

2) If you take a look at the "Expression" ... I am trying to come up with a formula that will calculate the "Current Ratio" by dividing Row-2[Value] with Row-1[Value], but I want that formula to ignore those situations where there is no value. 

Any comments/ideas would be greatly appreciated!
Thank you!

[mbarone]

row 33 is null.

formula says if row 33 is null and row 32 is null, which they are, then use row 33, which is null.  not quite sure what you're trying to accomplish.  maybe some more examples would help.

[pedrodrfaria]

Hi @dishamishra 

I have attached a workflow that I believe follows the logic you are trying to apply. Let us know if you were thinking something different. I wrote to a new field instead of updating the Value column for presentation sake. I used the field category to control when I'm calculating the Current Ratio

This was the function I wrote:

if [Category] = 'Current Ratio' and !isnull([Row-1:Value]) and !isnull([Row-2:Value]) then (([Row-2:Value]/[Row-1:Value])*-1) else null() endif

Please mark the discussion post as completed and assign an answer to the post (this helps us all). Feel free to reach out if you need additional help. We recommend to open a new discussion post if you have a different question.

Pedro.