Compare 2 measures in a mini chart

I recently published some hint and tips using mini-charts in Web Intelligence: http://scn.sap.com/docs/DOC-71192.

The objective of this new article is to illustrate different use cases of mini-charts.

Compare 2 measures in a mini chart

The objective of this document is to show horizontal bar charts in a table but with 2 measures on the same bar.

I still continued using special fonts and formatting to make mini-charts attractive and easy to use.

I also created input controls to dynamically change the conditional formatting and to simulate increase of the measure figures.

The Web Intelligence report displays on the same mini-chart Actual and Budget data in order to compare them.

To fully take advantage of “mini charts”, the idea is to display the chart in the text field using specific characters.

I created the variable “Tag empty” to display the values for Budget measure and “Tag full” to display the values for Actual measure. Those 2 variables are used in several other variables and contains only 1 character:

The character for “Tag empty” variable is using the (normal text) font (subset “Geometric Shapes”) with character code = 25CB: ○
The character for “Tag full” variable is using the (normal text) font (subset “Geometric Shapes”) with character code = 25CF: ●

The idea is to overlap the display of 2 measures in the mini-chart and to change the color when “Actual” is greater than “Budget”.

So if only full circles are displayed it means that “Actual” is equal to “Budget”, if the last circles of the mini-chart are empty then “Budget” is greater than “Actual” otherwise “Actual” is greater than “Budget”.

To display the mini-chart, I created a variable “Actual vs. Budget” to compute the difference between Actual and Budget and to display the right number of circles for each measure.

The variable formula is:

=If ([Budget] >= [Actual plan]) Then

If ([Budget]-[Actual plan] >= [Cells remaining]) Then

Replace(Fill([Tag full]; [Actual plan]); Fill([Tag full]; [Nb horizontal cells]); Fill([Tag full]; [Nb horizontal cells]) + Char(13)) +

Fill([Tag empty];[Cells remaining]) + Char(13)+ Replace(Fill([Tag empty]; [Budget]-[Actual plan]-[Cells remaining]); Fill([Tag empty]; [Nb horizontal cells]); Fill([Tag empty]; [Nb horizontal cells]) + Char(13))

Else

Replace(Fill([Tag full]; [Actual plan]); Fill([Tag full]; [Nb horizontal cells]); Fill([Tag full]; [Nb horizontal cells]) + Char(13)) +

Replace(Fill([Tag empty]; [Budget]-[Actual plan]); Fill([Tag empty]; [Nb horizontal cells]); Fill([Tag empty]; [Nb horizontal cells]) + Char(13))

ElseIf [Budget] >= 0 Then

If ([Actual plan]-[Budget] >= [Cells remaining]) Then

Replace(Fill([Tag empty]; [Budget]); Fill([Tag empty]; [Nb horizontal cells]); Fill([Tag empty]; [Nb horizontal cells]) + Char(13)) +

Fill([Tag full];[Cells remaining]) + Char(13)+ Replace(Fill([Tag full]; [Actual plan]-[Budget]-[Cells remaining]); Fill([Tag full]; [Nb horizontal cells]); Fill([Tag empty]; [Nb horizontal cells]) + Char(13))

Else

Replace(Fill([Tag empty]; [Budget]); Fill([Tag empty]; [Nb horizontal cells]); Fill([Tag empty]; [Nb horizontal cells]) + Char(13)) +

Replace(Fill([Tag full]; [Actual plan]-[Budget]); Fill([Tag full]; [Nb horizontal cells]); Fill([Tag empty]; [Nb horizontal cells]) + Char(13))

Else Replace(Fill([Tag full]; [Actual plan]); Fill([Tag full]; [Nb horizontal cells]); Fill([Tag full]; [Nb horizontal cells]) + Char(13))

This variable is also depending on another variable (“Nb horizontal cells”) that is driven by an input control to display the number of horizontal cells in the column.

There is an additional input control use to increase/decrease the Actual plan measure. This is used to simulate the evolution of Actual vs. Budget.

So we can play with the number of horizontal cells and the increment input controls.

Increment is added to the Actual to simulate evolution of the latter.

I also created conditional formatting to change the color of the mini chart:

Green, if Actual is greater or equal to Budget
Red, if Actual is less than Budget

To use special characters, I used Microsoft Word and copied the character in Web Intelligence formula editor.

To avoid too large mini-charts, it’s recommended to divide the Actual and Budget by a value (for instance 100).

Compare 2 measures in a mini chart

Compare 2 measures in a mini chart

Assigned Tags