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!

[patrick_mcauliffe]

On the Multi-Row tool, the settings from top to bottom go:

Update Existing Field

Create New Field

[Field Name]

Num Rows --> off to the right there's a drop down.

Click that dropdown and select the first option (NULL).

Is that what you're wanting it to do?

[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.

I had originally replied to your first post. Please let me know if that is not the solution you were looking for.

Pedro.