Charts play a crucial role in data visualization. Right from a simple line graph to a deeply insightful Sankey Chart, they are becoming an inseparable part of analytical reporting day by day. A good story spun around KPIs built on top of one (or more) dimensions and measures is all one needs to build a simple BI solution.
But at times, there are some reports with charts that use only measures but not any dimension like Country, Sales Territory, etc. We, at Visual BI, came across such a scenario and wanted to share it with you.
One of our clients wanted to migrate their BI frontend solutions from SAP Dashboards (formerly Xcelsius) to TIBCO Spotfire without compromising on the overall UI.
It was not as easy as it could be imagined. Why?
One of the best features Xcelsius offered was the flexibility in designing the layout, best fit for pretty much any business case someone could think of. This means one can position one visual on top of another (overlapping components) and make them look like one single component. This is useful in a scenario where multiple charts with different data sources are used to display just one KPI.
Another significant feature of the tool was, the underlying Microsoft Excel data source can be used to customize the data, just enough to fit the charts. A few years ago, with an ambitious roadmap, we got adventurous with the tool when we deployed our solutions to this client.
But in case of TIBCO Spotfire, dashboard real estate planning is entirely different. It relatively is a sharper, crisper, straitlaced tool powered by features to meet different goals. Without much space for layout customization (sans IronPython scripting) one cannot expect ‘chart-overlapping’ or ‘Excel modelling’ type of scenarios during live connections.
Some questions may arise.
- Why would one overlap components on a dashboard?
- Without MS Excel is it too hard to build a dashboard?
Consider this scenario. In the Oil & Gas industry, BOEPD (Barrels of Oil per Day) is an important metric. This must be shown as a group of measures viz., Sales, Budget and Production. Simply put, I need to compare Oil & Gas in three groups (Sales, Budget, Production) across various timeframes (MTD, QTD etc).
Oil & Gas Sales (S), Budget (B) and Production (P). Only measures. No dimensions.
In the image below, we have a stacked column chart used for that purpose. By leveraging Excel, data is modelled to fit in this chart.
For this to be replicated in TIBCO Spotfire, we had to use one stacked chart for each group. Refer (Before) Version below. Totally 12 charts (!). This defeats the purpose for on critical reason.
These individual charts, when observed together, are of different y-axis scales. YTD Sales (49) and YTD Production (320) are almost identical. Same height. One cannot compare the ups and downs of the measures at all.
A short and quick workaround did wonders. We came up with 3 simple steps (It is assumed that the reader of this blog is already familiar with the navigation of Spotfire Analysis).
1) New calculated column was created
- Data type: Integer, Value: -1
- Named it to ‘For Category axis space’
2) Add a Bar Chart
Properties window > Value Axis > Columns > Right click > Custom Expression
The calculated column was inserted between every group. This is to create a gap between the groups.
3) Set Min: 0 and click on Set to Current Range
This is to use negative summated values as gaps.
In one chart we have brought all the groups. Replicate this to the other charts (MTD, QTD etc).
Caveat: After ’Set to Current Range’ (without manually entering any max value) it picks the maximum number that chart has, at that point in time. In run time, once we start applying filters, the chart may go beyond that manually set max value. You may have noticed the YTD Production yellow bar slightly blurred on top. To avoid this, you can set a max value (that can never be surpassed) for all the charts to give a meaningful distribution of y-axis values.
This simple method that made a difference taught us one lesson. Every tool has its pros and cons. As an owner of a product or solutions it’s our responsibility to bring the best out of it. We cannot expect users to bend their requirements for limitations in the tool.
Happy dashboarding with TIBCO Spotfire 😊.
Got questions? Click here to get in touch.