Develop a what-if analysis with QlikView

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.

QlikView what if analysis example

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:

QlikView building connection script

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:

QlikView building what if chart type

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:

QlikView what if chart dimensions

Dimensions settings:
Used dimensions: =the_year & '/' & Num(month_of_year, '00')
Label: Time

Click Ok and in the next tab we add an expression to compute the selling margin (store sales - store cost):

QlikView what if chart dimensions

Expression settings:
Definition: =Sum(store_sales)-Sum(store_cost)
Label: Actual margin

To show the chart correctly the time dimension must be ordered, in this case by using a Text sort:

QlikView what if chart dimensions sort

The result in QlikView is a typical line chart, like this one:

QlikView what if chart

 

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:

QlikView what if variable

We place a slider component (Layourt -> New Sheet Object -> Slider/Calendar object) to allow the final user to set the vSellingPriceIncrement value:

QlikView what if slider

The slider parameters are:

Input type: Slider
Variable(s): vSellingPriceIncrement
Min value: -70
Max value: 70
In Caption tab set a caption of "Selling price variation (%)"

Now the QlikView document shoud appear like this:

QlikView what if slider new

 

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:

QlikView what if chart with simulated espression

The new expression contains the values:

Label: Simulated margin
Definition: 

=[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 what if chart result

 

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.

tags: