What-if analysis with QlikView
What if analysis is Business Intelligence techinque to simulate how historycal data (or estimated data) would have changed depending on some parameters. For example a manager can estimate how much the product margin can vary when some values as selling price or selling volumes changes. In a tipical business intelligence application this is achived by collecting hystorical data and applying a parametrized function with parameters set by the final user.
Some advanced what if analysis require a statistical background and some effort, however implementing a basic what-if analysis in a QlikView dashboard can be a simple task with impressive results.
The QlikView script
For this tutorial we use a database called FoodMart. The blog post Import Foodmart database in Oracle shows how to import the FoodMart demo database in Oracle XE, however the user is free to use any kind of database which contains some data.
The script to import data in QlikView is quite straightforward. Simply press File->Edit script and write the database connection and SQL query parameters used to load some data:
After pressing File->Reload the imported data is ready to be used!
Building a chart
The first chart is a standard chart created in QlikView by clicking Object -> New sheet object -> Chart.., like in this picture:
Chart settings are:
Type: Line chart
Window title: QlikView What if analysis
Show title in chart: unchecked
As dimension we'll format the time dimension as yyyy/MM:
Used dimensions: =the_year & '/' & Num(month_of_year, '00')
Click Ok and in the next tab we add an expression to compute the selling margin (store sales - store cost):
Label: Actual margin
To show the chart correctly the time dimension must be ordered, in this case by using a Text sort:
The result in QlikView is a typical line chart, like this one:
Adding what-if analysis capabilities
Now it's time to add what-if capabilities to our chart. First we register a new variable using Settings -> Variable Overview..
The variable name is "vSellingPriceIncrement", with ad definition value of 0, like in this picture:
We place a slider component (Layourt -> New Sheet Object -> Slider/Calendar object) to allow the final user to set the vSellingPriceIncrement value:
The slider parameters are:
Input type: Slider
Min value: -70
Max value: 70
In Caption tab set a caption of "Selling price variation (%)"
Now the QlikView document shoud appear like this:
The final step is to add a new expression which uses the vSellingPriceIncrement parameter to compute the selling margin. The final user can adjust vSellingPriceIncrement by moving the slider and the chart will update immediately.
To add this new line to the chart right click on chart -> Properties .. -> Expressions. Click on Add to define a new expression:
The new expression contains the values:
Label: Simulated margin
=[Actual margin] + (Sum(store_sales)*vSellPriceIncrement/100)
The final dashboard should be similar to the following image. When user moves the cursor on the slider the chart changes, showing a new margin line.
QlikView can be downloaded as a free personal edition which allows to edit and run qlikview files. A full commercial license is available for professional users.