model parameters using sum square difference minimization

[Josh_Metro]

I'm new to Alteryx, and I'm trying to replicate something that takes me only a few seconds to do in Excel. 

I'm try to fit a dataset to a model. In Excel, I do it this way.

This is the model (coefficients in bold): C = (Co-D) * e^(-k*t)

I set the starting values from the parameters

D	1
k	1

These are are my observations and my calculated C.

Co	t	C - observed	C -calculated
0	3	1.5	2
0.5	3	1.25	1.213
2	3	1.16	0.271
5	3	0.79	0.013
10	3	0.45	9E-05
30	3	0.02	2E-13
60	3	0.06	2E-26

I then calculate the sum square difference between the observed and calculated C values using the SUMXMY2 formula in Excel

SSD

1.85167928

Then I use the Solver to minimize the SSD by changing D and k. That literally takes seconds to do, and I get the best fit parameters for my dataset. How can this be done in Alteryx. I uploaded an example.

[Qiu]

@Josh_Metro 
The difficult part is the GRG Non-linear solver. 😁

[danilang]

Hi @Josh_Metro 

As @Qiu alluded to, it's easy to do in Excel, because all the complexity is hidden from you by being wrapped up in the  Solver add-in.    

The closest tool equivalent in Alteryx is the Optimization tool.  Unfortunately, I don't know enough about the GRG algorithm to be able to say if any of the available Optimizer options will produce the same output that you see in Excel.

Dan

[Josh_Metro]

Thanks, Dan. I played around with the optimization tool, but I couldn't make it do the same thing. It's possible I just don't know how to use it.

[danilang]

@Josh_Metro 

There's a very thorough example here. 

Dan  

[Josh_Metro]

Thanks, again, Dan. I did read that previously, but I couldn't figure out how to apply it to my use case.

[Qiu]

@Josh_Metro 

Agree with @danilang , we have to choose a mathmatical model first, then apply it to Alteryx.

It may not be difficult but just time-consuming.

What is the major challenge stopping you from just using Excel?

Or do you accept to call the Excel VBA and Altery will just to call the VBA if your calculation of sum square difference minimization is part of big workflow?

[Josh_Metro]

Hi Qui - the reason I was hoping to migrate the workflow to Alteryx is because I want to be able to apply this model to live dataset in Pi Asset Framework. I would schedule to workflow to run at a frequent interval, pull the parameters, and use them in a predictive model to inform operations of a wastewater plant. We are currently using Alteryx to read and write to our Pi database to perform calculations on live data that are too cumbersome to be done inside Pi AF. Working with my internal team, we were able to write a custom R script to do it (using the nlsLM function), but I was hoping for a more transparent solution. 

[Josh_Metro]

Just wanted to close the loop on this case. I ended up inserting an R script to achieve this objective.  I would have preferred native tools, but could not figure out how. This is the relevant part:

# Define function
f <- function(CT,b,kp,kd) {-log10((1-b)*exp(-kp*CT)+b*exp(-kd*CT))}
# Fit nonlinear model
fm <- nlsLM(Ratio ~ f(CT,b,kp,kd), data=data, start=c(b=0.9,kp=0.005,kd=0.05))
print(fm)

[rajdaiya]

Hi @Josh_Metro :

I am looking for a solution for EXACTLY the same problem. Would you be able to share your workaround alteryx workflow solution (including where u placed the R script) with me? Would really appreciate any help I can get on this