Concept study: Monte Carlo simulation in QlikView - two challenges for user interaction
Dear fellow community members:
I am doing a little research to build a draft application that includes an interactive Monte Carlo simulation in QlikView. The idea is that users can modify some values with slider-objects and that the whole model will be resimulated and other objects are updated on the fly. However, I come across a few challenges that I do not have an easy answer to, but I thought somebody in the community might have some ideas.
Challenge 1:
In Monte Carlo random created variables create an important role; a formula for such an variable might look something along the lines of: NORMINV(RAND(),Demand_Mean,Demand_STDEV)). The beauty of such a function is that it creates everytime a different number, the disadvantage is that you can never be sure that creates the same number twice, which is a problametic issue if you want to include it in a calculation model for a particular scenario (e.g. profit). In the QlikView script values can be locked with the SET/LET funtion. Is somebody in the community aware of a method to set a constant value for a partiuclar variable in an objects expression. Once the expression is executed the value can be distroyed and repopulated for the calculation in the next dimesion. I am thinking along the line of expression scripts/functions like (I know this script will not work in QlikView's objects, but it helps you to get the idea):
AGGR(
SET var1 = NORMINV(RAND(),Demand_Mean,Demand_STDEV));
IF($(var1)>0,$(var1),0)
Distroy var1;
, [Scenario])
For the next [Scenario] Var1 will be defined and distroyed again. One might suggest to place the entire function in the If-statement instead of the Var1 value. Unfortunately, this is not possible as the first might produce a positive value while the second produces a negative value and then the whole point of the If-statement is gone. 😉
Challenge 2 or better an alternative solution to rerun the Monte Carlo:
If the concept outlined in Challenge 1 is not possible, the following might be an alternative solution to meet the objectives. It is possible to calculate the result of a Monte Carlo simulation in the script, however this reduces the interactivity with the analysis for the user. However, the following might be an appropriate balance between interactivity and doing the heavy lifting in the script. The user can manipulate some values via input and slider objects and once the variables are set, he/she can press the a button, which executes a (partial) reload function. My question is whether it is possible to include values of variables modified via slider and input objects in a (partial) script reload. If this is possible, would somebody be able to explain the above the procedures to me.
In conclusion, a Monte Carlo based on the concept described in Challenge 1 has my preference but one based on Challenge 2 is certainly workable and I am keen to learn about methods to solve the above described challenges.
A promise: if I succeed in creating a workable Monte Carlo simulator with user interaction in QlikView I will document the concepts and share with the community.
Following up on our LinkedIn discussion I have put together a proof-of-concept showing how to do Monte Carlo simulation of 2 problems.
1) Calculating the value of pi through simulation
2) Calculating the statistical failure rate of widget manufacturing given user inputs.
Everything is done through the script and equations in these examples (within attached qvw file). I think this addressing the problem you lay out fairly clearly in an easy to implement manner.
The biggest issue I've run across so far is that the chart for calculating pi doesn't work well for more than 100,000 random instances. Running more than a million instances for either can bog down calculations and potentially crash the computer.
I would like to get the files that you referenced on Doncqueurs' Wall. I've been asked to create an application that uses Monte Carlo methods to answer the following questions: 1) What is the probability that a new product's cash flows will have a positive net present value (NPV)? 2)What is the riskiness of our investment portfolio?
I would greatly appreciate any examples of Monte Carlo simulation in QlikView.
