Over the past few years, we have become pretty accustomed to working with the likes of Power BI and Tableau on our projects, even offering this as part of a bonus feature on some of the projects we’ve recently been working on, therefore we thought it would be useful to explain a bit more about the work we do in this area and how useful a dashboard can be in simply giving a quick snapshot of results. See our summary following a recent expenses project:-
T&E Trends Overview: A high level view of expenses, as a whole, during the period of scope. The line graph in the middle and information just above it highlights the trends of expenses over time, giving the % change in amount month-on-month. The long blue bar with two values, around $20-22,000K, give the total value of expenses in the latest year (i.e. Jan-Mar 2019) vs. the same months in the previous year (i.e. Jan-Mar 2018) to give a year-on-year view.
Expense Report Processing Time: This was to highlight where aspects of the clients’ controls and policies weren’t being met. For example, in this case, expenses should be claimed within 25 days of their receipt date before users incur late fees. Line managers should then be approving their corresponding employees’ expenses within 7 days, and the Rejections Team (who act as like a secondary approval level) should accept/reject expenses within 2 days. The visualisations below serve to highlight this.
Hotel and Airline Booking: This dashboard highlights various combinations of booking/departed/returned dates by users, again to highlight where controls weren’t always working as they should (i.e. departed date for business travel should not be before its booking date, what reasons would users have to travel just for the weekend etc.).
Credit Card Limits: This dashboard was requested to highlight where users with company cards were, on average, underusing or overusing them based upon their corresponding credit card limits. The scatter graph shows each users’ company credit card limit against their average monthly spend on their company card (average taken from only their number of active months, not total months in scope, as some users may be infrequent travelers but require a large credit limit when they do travel, for example).
Potential Duplicates: A variety of duplicate expense testing was performed. The two shown in the dashboard below are just two of those combinations of fields used, using fixed level of detail expressions within Tableau. The drill down capability for Tableau is shown using this dashboard later.
Drilling Down into the Data
Viewing the data that makes up certain aspects of the visualizations is simple:
Hovering the cursor over the dashboard shows a customizable ‘tooltip’, where more detailed information can be added to both free up space on the dashboard and retain a desired level of detail at first glance.
Clicking on an aspect of the dashboard brings up the option to view the data behind a certain part of a visualization. The user then clicks the ‘view data’ icon shown as multiple lines (symbolizing individual records).
Once the ‘view data’ button has been clicked, a pop out window appears detailing the records that make up that specific part of the visualization, detailing all the field information present within the data source.
If you would like to know more or are interested in receiving further information in relation to the work we’re currently doing with Tableau or PowerBI or if you would like us to add a dashboard or two to give a high level review of your projects, please don’t hesitate to contact us! enquiries@dataconsulting.co.uk
