What-if analysis

[Former Member]

What-if analysis

We may often require to do what-if analysis in Business Intelligence.

Web Intelligence offers some capabilities to address this requirement.

For instance, we can dynamically increase or decrease measures value using input controls. Then we can manage the change through conditional formatting, filters, etc.

In this document I will illustrate how we can do What-If analysis by moving dimension values.

To illustrate the purpose, I will take as an example a Sales Organization.

In my Sales Organization (very simple!), there are Sales Representative linked to regions (North, East, West and South).

The dataset also contains the sales revenue per each Sales Representative like the below screenshot.

You can notice that the sales revenue per region is different. So my objective is to obtain around 25% of sales revenue per region next year.

So to obtain that result, I will do What-if analysis by moving the sales representative in the regions.

Move the sales representative in the regions

Create the hidden blocks and associate input control

I need to create 3 hidden tables. Each table only contains the Sales Representative. There will be:

• 1 table for “North” named “North”
• 1 table for “Est” named “Est”
• 1 table for “West” named “West”

There is no table for “South” and I will explain why later in this document.

Now I need to create 3 input controls based on Sales Representative and each input control is liked to one of the hidden block:

• 1 input control for “North”
• 1 input control for “Est”
• 1 input control for “West”

Create the variables

Now I created a variable to extract the filters from the current report.

This variable is named “Current Report Filter” and contains the following formula:
=Replace(Replace(Replace(Replace(Replace(ReportFilterSummary(ReportName());Char(13);"");Char(10);""); "{ "; "{");" }"; "}"); ", "; ",")

The formula takes the data from the ReportFilterSummary(ReportName()) function:

• ReportFilterSummary()
• ReportName()
• ReportFilterSummary(ReportName()) contains all the filters for the current.

Then I removed all line feeds, and remove redundant spaces.

Then I created a variable per region filtered by an input control (North, East and West):

• North filter:
  =If Match([Current Report Filter];"*Filter on Block North:*") Then Substr(Substr([Current Report Filter];Pos([Current Report Filter];"Filter on Block North:");Length([Current Report Filter])); 1; Pos(Substr([Current Report Filter];Pos([Current Report Filter];"Filter on Block North:");Length([Current Report Filter])) ;"}"))
• East filter:
  =If Match([Current Report Filter];"*Filter on Block East:*") Then Substr(Substr([Current Report Filter];Pos([Current Report Filter];"Filter on Block East:");Length([Current Report Filter])); 1; Pos(Substr([Current Report Filter];Pos([Current Report Filter];"Filter on Block East:");Length([Current Report Filter])) ;"}"))
• West filter:
  =If Match([Current Report Filter];"*Filter on Block West:*") Then Substr(Substr([Current Report Filter];Pos([Current Report Filter];"Filter on Block West:");Length([Current Report Filter])); 1; Pos(Substr([Current Report Filter];Pos([Current Report Filter];"Filter on Block West:");Length([Current Report Filter])) ;"}"))

The formula search for the position of the hidden block for the concerned region in the variable [Current Report Filter] and extracts a substring until the position of the end of the filter “}”

Then I created a second variable per region filtered by an input control (North, East and West). The goal of this variable is to check if a value has been filtered in the hidden block and then associate it to the concerned region.

• North selected:
  =If Match([North Filter]; "*{" + [Sales Representative] + "}*") Then "North"
  Else If Match([North Filter]; "*{" + [Sales Representative] + ",*") Then "North"
  Else If Match([North Filter]; "*," + [Sales Representative] + "}*") Then "North"
  Else If Match([North Filter]; "*," + [Sales Representative] + ",*") Then "North"
  Else "Other"
• East selected:
  =If Match([East Filter]; "*{" + [Sales Representative] + "}*") Then "East"
  Else If Match([East Filter]; "*{" + [Sales Representative] + ",*") Then "East"
  Else If Match([East Filter]; "*," + [Sales Representative] + "}*") Then "East"
  Else If Match([East Filter]; "*," + [Sales Representative] + ",*") Then "East"
  Else "Other"
• West selected:
  =If Match([North Filter]; "*{" + [Sales Representative] + "}*") Then "West"
  Else If Match([West Filter]; "*{" + [Sales Representative] + ",*") Then "West"
  Else If Match([West Filter]; "*," + [Sales Representative] + "}*") Then "West"
  Else If Match([West Filter]; "*," + [Sales Representative] + ",*") Then "West"
  Else "Other"

Last I created a variable that will be the new region of the Sales Representative.

The problem is that each input control is based on the same dimension, so a same sales rep can be selected for multiple regions, and I don’t want that.

So the formula will take into account this behavior:

• I decided to move to North region all sales representative selected by the associated input control
• Then I moved to East region all sales representative selected by the associated input control and that have not been selected by the input control associated to North
• Then I moved to West region all sales representative selected by the associated input control and that have not been selected by the input control associated to North and the one associated to East.
• Last I move to South region all sales representative not selected for North, East and West. This is why I didn’t create a block and an input control for South.

The formula is the following:

=If [North selected] = "North" Then "North"

Else If [East selected] = "East" And [North selected] = "Other" Then "East"

Else If [West selected] = "West" And [East selected] = "Other" And [North selected] = "Other" Then "West"

Else "South"

Now I can play with the input controls and move the different Sales Representative to reach my initial objective.

We can compare now the initial sales representative contribution per region to the new one.

You can download the Web Intelligence report attached to that publication.

Didier MAZOUE