What-if analysis via. data-modifying macros

[rothtd]

QlikView Guru's:

I am interested in your thoughts on combining load script inputfields and data-modifying macros to allow for complex what-if simulations to be run as an alternative to using Set Analysis.

Under complex what-if simulations Set Analysis seems to become increasingly difficult to implement. The following "change the data" technique is not difficult to implement (if you're a good macro coder) even for very complex what-if situations; however, since it utilizes macros is unstable as your data model grows.

The idea is to load copies of specific fields and designate them as alterable (using inputfield in the load script). This would allow the user to select a dataset, change specific data for that dataset, and see the result - comparing the data in its altered form to the data in its original state to see the effect of the what-if situation. The user would then repeat the process to run an infinite number of what-if scenarios, and when complete view the impact of this entire simulation on the data (that is the combined impact of all their scenarios).

My real-life example is using Microsoft Project Server 2008 data. With this data, a specific project's tasks can influence tasks on other projects (because the same people may work on multiple projects causing a dependency relationship). If a given task is not completed on time a ripple effect is created among all these decencies, causing additional project tasks (possibly on other projects) to be completed late. Modeling this complex behavior seems to be very difficult with Set Analysis but less difficult when using macros to alter the data. Unfortunately, this "change the data" solution isn't reliable at this time since QlikView has known stability issues when using macros in this manner.

Algorithm:

1) User selects a dataset

2) User presses button (ex. 'Cancel Project') which invokes macro (ex. 'Cancel Project Macro')

3) Macro looks at selected dataset (and any additional input needed) and modifies copies of the original data fields to new values. (ex: adjusting project start/end dates, resource availability, financial figures, etc.) Note that at this point the macro can include logic to find additional records that need modification outside the selected dataset (ex. project task dependencies in MS Project)

4) Repeat as many times as the user would like allowing users to use different buttons to run various scenarios, and use additional datasets.

5) Present data to user allowing them to compare the original unaltered data to the altered data so they can see the affect of their complex what-if simulation.

Below is a screen shot of my proof-of-concept application which works fine with a limited data load, but the macros fail when the complete set of data is loaded. The POC demonstrates the 'ripple' effect of canceling a project by comparing the original project/task beg and end dates to the altered "what-if" dates to show the potential savings (the Sim Hours Gained column).

Again, I am interested in your thoughts on this idea and any thoughts on getting macros to function properly as the data model becomes more complex.

Thanks in advance!

[#2]

I'm now trying to do something very similar to what Trevor described above. Addressing this is key to examining various scenarios, and I was wondering if anyone had any suggestions for this.

[rothtd]

I haven't yet found a solution to this challenge. Some colleagues of mine who are working on similar simulations stated that they use other tools to 'prep' the data for simulations, and then use QlikView to view the possible outcomes. I suppose this technique would work well if you knew in advance the different scenarios you wished to simulate/model.

[#4]

Any update on this question?

I'm looking to have a simple input field to simulate what-if analysis in QlikView.

Thanks!

Tyler