How to Mimic an Excel Butterfly Chart with Web Intelligence
Often users export data from Web Intelligence to Excel to achieve an appealing visualization. Or they craft their requirements with Excel and consequently ask for similar capabilities. No matter what tools are used to produce the final visualization, users expect that the person in charge of the development (in some cases the business user himself/herself) will provide a similar end product.
Although the SAP Web Intelligence chart engine has drastically improved in the course of the most recent releases, there are still a few business requirements that require a few tricks to achieve a captivating visualization.
With some creativity and a few tricks, you can achieve the same result with Web Intelligence and benefit from an enterprise solution (security, publication, etc.). Today I will guide you through the creation of a butterfly chart.
The butterfly chart above, embedded in a Web Intelligence table, is completely dynamic. The bars will always be aligned with the product lines. In addition, the visualization is interactive, allowing the user to select any individual state or all states. And if “drill” is enabled, the butterfly chart will still perfectly display the next level of data.
Such visualization helps integrate a chart in one table that quickly pinpoints the key data the business wants to focus on, and in the meantime, provides deeper information to better understand the why and support a story with relevant facts.
In our example, the butterfly chart displays the margin, including the total value. The butterfly chart is created using the following Unicode characters and the font Courier New (other fonts can be used).
- ALT 219 for -█-
- ALT 222 for -▐-
- ALT 221 for -▌
Prepare the variables
1. Create a measure variable “vSum Margin” to calculate the absolute value of the sum of our margin, using the formula =Abs(Sum([Margin]) In Block).
Because the margin can be negative or positive, it is important to calculate the absolute value of the sum so we can use it to define the length of the bar.
2. Create a measure variable “vMax Margin” to calculate the maximum margin in the table, using the formula =(Max(Abs([Margin])) In Block).
3. Create a measure variable “vMax with Total” to determine whether the sum of the margin is higher or lower than the maximum of the measure in the table, using the formula =If [vMax Margin]>=[vSum Margin] Then [vMax Margin] Else [vSum Margin].
4. Create a measure variable “vRatio Margin” to calculate a ratio used to define the size of the bar for all defined margin values, including the total margin, using the formula =(Abs(Sum([Margin]))/[vMax Margin with Total])*20.
The formula divides a specific measure, margin in this case, by the maximum value of the margin, including the total.The number 20 in the formula specifies the maximum length of the bar (20 characters). It can be adjusted to reflect any specific needs, but I would recommend keeping it small so you won’t have a butterfly chart that completely overflows the table. If the margin is 55 and the maximum value is 100, the calculated ratio is 0.55. This ratio is then multiplied by the maximum length of the bar (20 characters). The bar will be 11 characters long.
5. Create a measure variable “vRatio Round Margin” to round the bar ratio, using the formula =Round(Abs(Sum([Margin]))/[vMax Margin with Total]*20;0).
The variable will also help better display the bar when the ratio multiply 20 is between 0 and 1. This is often the case when there is a broad variation between the maximum value and the series.
Add the bars to the table
Our next step, after the creation of the variables, is to add the bars to the table.
1. Add two columns in the table, one for the negative bar (left) and one for the positive bar (right). I recommend adding those columns right after the dimension because it helps the user not only quickly identify the largest value, but also visualize the variability of the margin.
2. Add the following formula to the negative column: =If Sum([Margin])<0 Then If [vRatio Round Margin]=0 And [vRatio Margin]>0 And Sum([Margin])<0 Then “▐” Else Fill(“█”;[vRatio Round Margin]). Paste the same formula in the total cell.
This formula displays X numbers of Unicode character “█” based on the ratio calculated in the variable “vRatio Round Margin.” If the ratio is between 0 and 1, the formula displays the Unicode character “▐”
3. Add the following formula to the positive column: =If Sum([Margin])>=0 Then If [vRatio Round Margin]=0 And [vRatio Margin]>0 And Sum([Margin])>0 Then “▌” Else Fill(“█”;[vRatio Round Margin]). Paste the same formula in the total cell.
4. Finally, format the two columns with relevant colors to identify negative and positive values, change the padding so the left of the negative bar is aligned with the right of the positive bar with no gap, change the horizontal alignment, and change the font to Courier New.
There is no such thing as “one size fits all” in business intelligence, but we can get close to “one size fits most” with creativity. Web Intelligence is not perfect but can answer a lot of your governed business intelligence with creativity and a few tweaks.
Now, if you want to explore and benefit from the whole gamut of tools available out there, maybe instead of picking a tool first, you should start analyzing the “style of computing” (or personas) in your organization. That’s the subject of a future blog.