Formula tool calculation error

[MHS]

Hello Community!

I am having an issue with the formula tool that I am unable to figure out and hoping that someone can help point out what is happening with my calculation.  I am attempting to add up the values from 4 prior multi-row formula tools to arrive at a sum, but Alteryx is calculating the values that are much larger than the sum of the pieces.  

The values for the open status duration, sprint ready duration, 3 amigos duration, and in progress duration are 6126481, 6126481, 0, 0 which adds up to 29758628.  The total story duration tool is providing me with a total value of 612648123632147?!  

For the life of me I cannot determine what is happening with the calculation and I am sure it is something simple that I am overlooking.  Any help with what is occurring would be greatly appreciated.  

Thanks, 

Matt

[DataNath]

Are you able to share your workflow and the data used for it by any chance, @MHS?

As a start I'd recommend checking your data types, though if they were wrong the formula may throw up an error anyway.

[Ladarthure]

Hi @MHS,

from your screenshots, I would look at the data types. If you want to add those values, make sure all of them are in numeric format (int, float, double) and then make your formula. which should end up having a data type numeric also

Tell me if it works!

[davidskaife]

Hi @MHS 

I've just done some testing and do you have the outputs from the multi-row formula tools set to one of the string data types? I get the below using your values with the data type set to V_WString:

You'd need to change them to one of the numerical formats, plus the final formula tool output for it to work correctly

[MHS]

Hi DataNath,

When I attempt to modify the data type output to an integer, bool, or byte the multi row tool throws an error that the calculation resulted in a string, but the field is numeric.  Maybe I need to adjust the statement in the tool before it will allow me to output it in one of these data types?

Thanks for the help!

Matt

[DataNath]

@MHS in your first multi-row formula (Open Status Duration), you have the final outcome wrapped in quotations which will force this field to output as strings rather than numerical. From a quick look through I can't see any other instances but maybe worth a good double check. After that just ensure all multi-row formulas are set to output as numeric data types and try the formula again!

[MHS]

Thanks for the quick response @DataNath !

Tried your suggestion and continue to run into the error.  Adjusted the statement a couple ways and even removing the last condition and the error persists. Trying to output as an INT64 since I will be measuring in the millions.

[DataNath]

Anything you wrap in quotations Alteryx will see as a string, including where you're using THEN "" or ELSE "" to output a blank. If it works with your requirement, can you change these to THEN NULL() and ELSE NULL()? That will still allow the output to be numeric.

[Qiu · Accepted solution]

@MHS 
I think I found the cause.

For the column "Issue ID match?", I believe you have input as "True" or "False", which is treated as Bool in alteryx rather than string.
So we need to use the boolean expression when doing the conditional statement as below.
Hope this will resolve your issue.

[MHS]

Thanks @Qiu !

I replaced the True / False values with 1 and 0 and was able to change the data types to INT64 which produced the correct calculations.

Matt