Poison Inverse Calculation Formula?

[SAMMILU]

I am working on a model which needs to use poisson inverse calculation to get the final number X, X=POSSINV(P, Lambda), and then input the X to another workflow to continue another batch of calculation. Currently I using Alteryx workflow to gather all the number and run complicated calculation to get the P and Lambda first, however, Alteryx does not have Poisson Inverse Formula installed while Excel has. So I have to output the data into a excel file which has formula insert.

As the pic below, The Alteryx output would be column A and B, Column C with Formula inserted should be updated everytime after the number is changed. And then I input the same excel file including the updated Column C to the second workflow. However, the issue is that the excel Column C is not automatically updated at the input of the second workflow. After the first workflow finishes I have to open the excel file and the save and close before running the second workflow. It gives me headache as I am unable to put it schedule and I have to manually do it every morning. Does anyone have the similar experience? And how should I solve it? I think I would either need to add the poison inverse formula in Atleryx, which I don't know how to, or get some way to reflesh the excel file before it input to the second workflow. Thank you for your input!

Sammi

[jdunkerley79 · Accepted solution]

Assuming you have the predictive tools installed you can use the R tool to compute the inverse poisson:

IN <-read.Alteryx("#1", mode="data.frame")
Out <- data.frame(Prod=IN$Prob, Lambda=IN$Lambda, Inverse=qpois(p=IN$Prob, lambda=IN$Lambda))
write.Alteryx(Out, 1)

Sample attached

[SAMMILU]

Thank you so much for your help! However, I am unable to run the sample and it gives the error below. I am a beginer of Alteryx and not sure if I have the right tool installed. Do you know how to solve it? I am using verson 2018.2 x64.

[jdunkerley79 · Accepted solution]

You need to install the predictive tools to get R tool.

It should be available for download from licenses.alteryx.com

[SAMMILU]

Thank you so much! It works very well! However, when there is a non-numeric column in the input as below, it can't run thru. Looks like the input model needs to change to metainfo. But I am not sure how to modify the rest of the script. Could you please help me out again? Thank you so much!!!

[jdunkerley79 · Accepted solution]

Try:

IN <-read.Alteryx("#1", mode="data.frame")
IN$Inverse <- qpois(p=IN$Prob, lambda=IN$Lambda)
write.Alteryx(IN, 1)

This works by adding a column which I think is a much cleaner solution.

[SAMMILU]

Thank you so much! It works perfect in the workflow. But, one more issue, it looks like the R-TOOL is prohibited after I uploaded the workflow in the Gallery for scheduled run. Do we have any altertive tool which could use this qpois formula?

Thank you so much for your patience!

Sammi

[jdunkerley79]

Only thing I can think of is to do it using the Python tool but it might have the same limitations.

[SimTaylor]

This is fantastic - thank you James.

Is there a way to make some minor adjustments here to get the same using the negative binomial distribution?

Context - I am totally new to R (in fact, not even started) but this tool would be very helpful for testing whether poisson / negative binomial distribution is better for modelling demand variability for some products vs. normal distribution.