Alternatives to EXP command

[abaker1997]

A process I am running includes - as part of a series of formulas - one step that puts raw_score through a logistic function: raw_exp=EXP([raw_score])/(1+EXP([raw_score])). This process previously was run on raw_scores that ranged from -200 to -2, and worked fine.

However, the formula behind raw_score has now changed, and its output now ranges from 25 to as high as 12000. This poses an issue when reaching the raw_exp function, because apparently for any values of raw_score larger 709.7 or so EXP(raw_score) returns a null value instead of the actual result, which throws off the distribution of the final score downstream.

Is there any way I can get Alteryx to handle values larger than 709.7 in the EXP command normally? Alternatively, is there a tool I'm missing that would do the same thing the raw_exp formula is doing without the potential for errors, or a potential mathematical brute-force to make every raw_score value be lower than 709.7 (i.e. multiplying them all by 0.01)? The result of raw_exp gets rescaled to be from 1 to 100 anyway, so I don't think multiplying raw_score by 0.01 will have any mathematical downsides since I'd be applying it to the entire universe of scores, but I may be missing something.

[flying008]

Hi, @abaker1997 

Try this formula: (If you want get more digit, use e as 2.71828182845904523536 )

raw_exp = Pow(2.718281828459045, [raw_score])/(1+Pow(2.718281828459045, [raw_score]))

[abaker1997]

Appreciate the suggestion, but still doesn't seem to work for raw_score values over ~709.7. Guess my question is now more about how to work around the apparent overflow limit than alternatives to using EXP for the calculation I need done. 

In that regard, do you know if I'd be fine multiplying all raw_score values by 0.01 to force them to all be under 709.7, since I'm going to end up rescaling raw_exp outputs afterwards anyway through a bound command to be from 0-100.

[apathetichell · Accepted solution]

I do not believe you can multiply your numbers by .01 and get it to work. Try it with 1 vs .01. Having said that - your rounding is really only effecting your exp field. Your other field is so statistically close to 1 that I would just call it 1. 308 and change is the max you can get out of the double type in C/Alteryx. You can try longints in python. you'll have like 300 digits but whatever. You will not be able to use the Math.Exp function in Python because this is beyond the range of the Float type.

I'm not really an expert in this stuff - but I kind of think that this equation is not returning meaningful results (whereas it was from -200 to -2)

[abaker1997]

Fully agree with you on that 2nd part - the raw_score formulas were recalculated by a client, who somehow managed to generate a usable series of 0-100 scores for their test dataset of 5K records or so in R even though I can't see how they were able to pull anything meaningful from the raw_exp function. I'd follow their same approach and do this in R, but I need to run this on the full dataset of ~250M records (along with a bunch of other modifications to the dataset that are already set up to take place in Alteryx) so I'm a bit hamstrung as to how I can approach this. Will try and get more info tomorrow, appreciate the help so far.

[apathetichell]

Check out this stack overflow discussion:

https://stackoverflow.com/questions/73898704/large-exponent-values-in-r-with-rmpfr