The following guest blog is written by Vadim Rutkevich, Business Analyst at StiltSoft.
An advanced user of Microsoft Office can feel a bit frustrated once he moves to Atlassian Confluence. The conventional tools for table management are not present in the full extent in this team collaboration platform. Though the rich Atlassian Marketplace provides a variety of tools and apps to extend the native functionality with smart and robust capabilities.
If you’re already used to formulas, cell formatting, filtration of complex data, pivot tables, and dynamic charts, you cannot miss the two apps on the Atlassian Marketplace. They are Spreadsheets from Valiantys and Table Filter and Charts from StiltSoft team. In this post we will show you how to work with them in junction and achieve greater results in table analysis in no time.
In this blog post, we will feature a use case that can be quickly and easily accomplished with Spreadsheets and Table Filter and Charts apps.
So let’s get started!
Spreadsheets for Confluence
Out of the box Confluence does not support Excel spreadsheets. Of course, you can copy their content on the page, but that’s all. This way you can ping-pong your files between Confluence and Excel, but it is quite inconvenient and time-consuming.
The Valiantys team has designed the Spreadsheets app to support Excel workbooks, preserving their current cell formatting along with using formulas for dynamic data calculation. The available cell formatting toolset is quite sufficient to highlight the specific data arrays and indicate threshold values among the ones within specification limits.
You can either create a new spreadsheet on the fly or import the existing Excel book with all its formulas and formatting into Confluence. Valiantys team did their best to preserve the refined Excel experience in Confluence and keep all the most widely used features. When dealing with data you may want to perform some calculations or analytical operations, and here you can take advantage of hundreds of formulas supported by the app.
Table Filter and Charts for Confluence
Table Filter and Charts is an app that can greatly simplify evaluation of table data and its further transformation into a clear and straightforward interpretation. So what capabilities does this solution provide?
First of all, you get a variety of options for filtering almost any kind of data that can be created and managed in Confluence, as follows:
- Dropdown, free text and global filters search for specific text queries and values.
- Number range filter looks up numerical arrays and time intervals.
- Date range filter finds data over certain time periods.
- Visual filter filters icons, emoticons, check boxes and images.
Tracking monthly product orders
Product orders from different office supply stores are tracked in a single Excel spreadsheet. At the end of the month, the collected data is passed to a sales manager who further imports it in Confluence through the Spreadsheets app. The sales manager inserts the Spreadsheets macro onto the page and selects the file to upload.
The resulting spreadsheet contains the following columns:
- Order date – date when products were ordered for the store.
- Store – number of the store where the products will be delivered.
- Item – defines the product item which is ordered.
- Regular Price – price for one unit.
- Order Qty – the number of ordered items for the store.
The original file contains only raw data, though the sales manager needs to do some calculations with the Spreadsheets app. First, he applies the currency format to the pricing column and then inserts a new column for calculating the wholesale price for supplies with over 50 units ordered. The manager can quickly copy the formula to the rest of cells by dragging it down.
Afterwards, the manager adds the Subtotal column and applies the formula with the IF condition for calculating subtotal for ordered items depending on their regular or discount price, as follows:
The manager applies this formula to the rest of cells in the Subtotal column. Afterwards, he decides to evaluate revenue from each store, getting insights into how the orders are structured.
Filtering product orders
Initially, the sales manager wants to see product items ordered by each store. He places the Spreadsheets macro within the Table Filter macro and adds a dropdown filter in the page view mode. The manager also uses the free text filter to view data about specific product items.
Aggregating revenue data for stores
For summarising revenue for each store over each month, the sales manager adds the Pivot Table macro and moves the current set of macros within it. Then he adds the thousand separator for better look of numerical values, adjusts the date format, and selects the appropriate period for data aggregation. After saving the macro and the page, he builds the pivot table.
He selects Order Date as a row label, Stores as a column label, Subtotal as a calculated column, and changes the operation to Sum. Below is the pivot table showing the revenue from all stores by month.
But it is quite difficult to understand whether there is an increase or decrease in revenue. So the sales manager decides to visualise the resulting table with the time line chart.
Visualising revenue from stores with a chart
The sales manager edits the page and inserts the Chart from Table macro which will wrap the currently used combination of macros. While editing the macro, he sets the chart type, defines the date format as ‘m/yy’ for correct operation with aggregated monthly data, and enables the trend lines for showing the prevailing direction of revenue from each store. Once the page is saved, the sales manager adds the columns with aggregated values and adjusts the dimensions of the chart. If needed he can temporarily disable display of information about specific stores.
As a result, he gets a time line chart showing the monthly revenue from each store along with the trend lines indicating the increase or decrease in the revenue over the six months. So now the sales manager knows which stores require more attention by looking at the decrease in the revenue.
Using in Junction
Combining both Spreadsheets and Table Filter and Charts apps can give you a lot of benefits:
- excellent management of spreadsheets in Confluence;
- retention of the cell formatting and formulas during file import;
- support for different arithmetic and analytical operations on cell data;
- instant filtration of the spreadsheet and the ability to search for dynamic ranges of numerical or date values;
- aggregation of raw and calculated data in multidimensional pivot tables;
- visualisation of data with dynamic charts and graphs indicating data trends and threshold values.
If this seems like a useful use case, you can try Spreadsheets and Table Filter and Charts apps on the Altassian Marketplace.