POC: Multi Scenario What-If Analysis

[rothtd]

I am not sure if anyone else is doing this kind of What-If Analysis so I figured I would do a proof of concept to explore it. If any of you have time to give me some advice I would greatly appreciate it.

EXAMPLE:

Open the attached QlikView application and do the following:

1) Clear all selections.

2) Pretend you are the end user…

3) You think District Manager Bill Anderson over projected. Drop his projections 10% for the entire Fiscal Year.

3a) Select DistrictManger=Bill Anderson, enter -.10 in the input box, press return to set the input box value and press the 'Apply WhatIf Scenario' button. Clear selections when finished.

4) You think the state of Texas will pass a taxation law (starting in Fiscal Period 5) that will impact your sales. Drop projections for all stores in the state of Texas, for fiscal periods 5 through 12, by 7%.

4a) Select State=Texas, FiscalPeriod = 05-2010, 06-2010, 07-2010, 08-2010, 09-2010, 10-2010, 11-2010, 12-2010. Enter -.07 in the input box, press enter to set the value, and press the 'Apply WhatIf Scenario' button. Clear selections when finished.

5) You think the East region will experience above average temperatures, increasing sales. Increase projections for all stores in region East by 15%, for only fiscal periods 6 through 8.

5a) Select Region=East, FiscalPeriod = 06-2010, 07-2010, 08-2010. Enter .15 in the input box, press enter to set the value, and press the 'Apply WhatIf Scenario' button. Clear selections when finished.

6) You think the Midwest District will experience extreme cold temperatures decreasing sales. Decrease projections for all stores in District=Midwest by 30% for Fiscal Periods 1 and 2.

6a) Select District=Midwest, FiscalPeriod = 01-2010, 02-2010. Enter -.30 in the input box, press enter to set the value, and press the 'Apply WhatIf Scenario' button. Clear selections when finished.

7) Clear all selections and examine your new projections for the upcoming fiscal year.

😎 Now use the analytic capabilities of QV to examine the data.
8a) During what periods did your What-If projected sales rise?
8b) During what periods did your What-If projected sales fall?
8c) Examine period 1 and 2. What district manager should you be talking to if you want to bring your What-If Projected Sales numbers in line with the original Projections?

CONCEPT:

Using the 'inputfield' key word in the load script designate a field as modifiable. Write two macros: one that modifies the designated field according to your custom logic, and a second that resets the value of the field back to its original state (these macros are mapped to two buttons on the user interface).

Instruct your end user to select a dataset using whatever criteria they wish (i.e. make a bunch of selections in QV) and then press a button to apply the what-if analysis condition to that set of data. In my example I have also added an input box. My macro will find each modifiable field in the currently selected dataset and multiply it by the value in the input box. From the end users perspective this translates to increasing/decreasing projected sales for the dataset by the amount in the input box. Finally provide a view the user can use to see the outcome of the changes - such as a graph showing the original value vs. the modified value of the field.

BENEFITS:

- Easy to understand for the end user.
- Can apply unlimited amount of complex What-If scenarios.
- Seems like this general 'change the data' approach to What-If analysis could be used in many different circumstances.

ISSUES:
- The application seems to be buggy. I'm not sure exactly what is going on, but it seems that the reliability of this concept breaks down as either: the volume of data increases, the complexity of the data model increases, or the complexity of the selected set criteria increases.

ADDITIONAL NOTES:
- Application was written in QV version 9.0, SR3. To my knowledge I am not using any functionality that is specific to 9.0 (that is it should work with 8.5).
- The application was written quick and dirty as a proof of concept, please forgive any coding, design, or documentation errors…

If any of you are interested, please have a look at this application and the general concept it presents. I would be especially interested to know if anyone is doing this type of work in QlikView, and if I could get advice for making this concept more scalable.

Thanks in advance!

[#2]

Trevor,

I haven't gotten into a what-if analysis yet where I would use this, but the idea is great. Today I am just using variables and including those variables in any expressions with the "real" data. (IE: Sales+SalesVariable=Sales) Your solution would let me move the what-if into the script, and also seems like it could allow for more minute changes than a broad "add 5%" situation.

One other interesting component I saw was the ability to build upon the what-if. In my method, you can select a % to add to Sales. With yours, you could add 500 to sales, store that as a projection, then add another 500. By storing the what-if back in the data, you can work with it as a data-set on its own.

Great work!

Wes

[vidyut]

Good work Trevor. Application is really a good one to demonstrate the What-If analysis using input fields.

Regarding input-fields, there are couple of issues which should be taken care of:

1. When you reload the data, the value in the input field is preserved. Time-to-time it creates confusion for the users if they are not used to this.(Clear All What-If Scenario button helps there ).

2. Providing a button to export the scenario so that the analysis could be preserved/stored.

Thanks again for posting this application.

[rothtd]

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

Vidyut:

Great comments. Sounds like you have some experience using Input fields - do you use macros to modify them, or a different method? I am noticing that my example application does not scale well - just wondering if you have a different method that works better.

Thanks again!

Trevor

[#5]

Trevor -

Great what-if analysis but when I click enter nothing happens. When I click on apply button the macro opens up. I close the macro and the numbers still change but can't get pass that point. Do you have  a revised example?

Thanks

[#6]

Hi,

I am attempting to essentially replicate what you'e done here in my own dashboard and you're model is fantastic for achieving an accurate what-if analysis.

However I am struggling to get mine to function as yours has and am having numerous issues with the functionality of the macro.

i was wondering if you possibly ever modified or built on this qvw for a clean functioning dashboard?

I would be extremely grateful for any help on the matter at all!!!!

thanks,

James

[rothtd]

James – while the concept of this is awesome, the practically of it seems limited due to its dependence on macro functionality (which has known issues, and is deprecated [I believe]). I gave up, but an intern of mine ran with it and made some very interesting enhancements.

For the purposes of her work, she switched from applying percentages to the input fields to simply true/false flags. In this POC the customer was interested in a grouping/segmenting scenario, not a forecasting scenario – but the concept/implementation is identical. She seemed to have fixed the stability issues – but we didn’t conduct any in-depth testing to verify that. Secondly, she created a unique method to allow for “roll back” of simulation selections. Where I was using a single true-false flag per row (or percentage in this thread post), she simply created a chained-string of flags (ex:  true|true|true|false), allowing for the end user to roll back the application of a specific scenario by hitting a ‘undo’ button. The charts would look at only the right most value (most recent) – it’s a cool way of storing history. To top it off, she added a toggle for if you want to apply flags as a “or” or “and” operation (regarding the results of previous scenarios) – probably more relevant with true/false vs. percentages. It is all really cool to think about – but I’ve always kept it as a ‘proof of concept’.

I’ll include some attachments – maybe it will help you.