A CEO of a California distribution company builds a sales dashboard with three views: a detail table filtered by month, a dual-axis trend combining website visits and sales, and a bubble map of invoice size by state. The video shows how to assemble them into an interactive dashboard with touch filters and export data to Excel.
Questions this video answers
April 8, 2018.
Over 11 million sales transactions.
SAO sales revenues (sample data source).
"A couple of hours."
The trial license does not allow saving dashboards.
Chapters
The CEO of a distribution company in California needed a sales dashboard for recurring meetings with the sales team. The dashboard needed to show this month's sales by customer by invoice number and product. Also a chart showing how website marketing campaigns or affecting actual sales over time and then sale size by ship. To address, the CEO went to data self data sources and picked SAO sales revenues. Clicked on these three dots and selected new workbook. The data panel on the left is a sample for trial purposes. You can find dimensions and measures. And by the way, this sample database has over 11 million sales transactions. It's a decent sized sample database. Let me show how the CEO created the dashboard from scratch. The first part is monthly sales by customer, invoice number and product. So let's do it. Let's find customers, drag and drop into the rows area. These are all the customers. Let's take sales, drag and drop into this area right here in the middle and we see total sales in the whole company history. Who's the top customer? Let's sort descending. There it is. These are the top customers. How much is the total? Let's add a grand totals for the columns and let's put it at the top so it's easier to see it. There it is. Now when I see this this month. So let's add invoice date to columns and right now it shows my whole sample database historical data I want to see by month. So let's drill from year, click the plus signal to see quarter. Let's click the plus signal on the quarter to go to the month level. And now we have the whole company history by customer by month all the way to the current month. Well, we only want to see this month. So we're going to filter this report for only this month. Let's take invoice, date, drag and drop into filters in here in the filters. Let's click this arrow point down and select relative date in qlik relative date instead of today, let's show months instead. And there it is. This report is showing sales by customer this month. If you want to change the filter later and let's say see previous month. There it is. Maybe the last, I don't know, three months, two months. You just change this little wizard. You can pick weeks, quarters, years and dates. It's very easy to change the filter. Let's go back to this month. And now that I'm only looking this month, I don't need to see this 2018 quarter and month. Let me just remove the headers to be simpler, cleaner view just Cleaning it out. Okay, good. Now I want to see sales by customer, by product and invoice number. How do I add more pieces? You pretty much find them here. So let's say product, product description, drag and drop after customer in my rows. And there it is by customer, by product I have sales. How about invoice number? Same thing. Come here. Find invoice number and drag and drop at the beginning, the middle at the end. Just drag and drop I'm going to put in the middle. So by customer, by invoice number, by product is sales. Maybe you want to see subtotals. We come here and select, add all subtotals and there it is. By customer, subtotal, by invoice number, subtotal. It's that easy. Drag and drop. So this is the first time, the first part of the report of this dashboard. The second part of the dashboard needs to combine sales and website visits together. Now website visits come from Google Analytics and sales from the ERP system. Before data self, their marketing and IT team were spending a couple of days per month putting this trend analysis together. Now, well then we then blended Google Analytics data with their ERP in their data self data warehouse. And now website visits is automatically refreshed along with the sales data every day. And it's all together as part of the SA revenues data source. Let's see how the CEO built this section dashboard from scratch. So when I create a new sheet and see sales, I double click sales, I double click invoice date and for the year I'm going to choose instead month. So right now it's showing sales trend for the whole company history. And then we take website visits, we drag and drop right here before sales. So website visit is here, sales is here. We click show me and select the dual access approach. And we hide this show me button. And let's make these bars slightly narrower. So what we get is sales are the bars in blue. A website visit is the orange line. In their case, this is simulated. In their case, every marketing website marketing campaign would create a spike in website visitors. And usually about a month or so ago later those sales would actually respond going higher. Okay, that was easy. Automatically refresh every day. This report now is ready. The last part of the dashboard is sale size by ship to state. So let's create a new sheet and select invoice size, ship to state and sales. So I use the control button to select those three fields. I go to show me and select the map, one of the map options. Now I'm going to make this a little bigger and make the bubbles a little bigger. That's it. With a few clicks I built this nice report that shows bigger bubbles, bigger sales and the bubbles are sliced by large, medium and small invoices. The aggregation of large, medium and small invoices by ship to location. Finally, the CEO needed to put these three views into a single dashboard like this. Now we're going to create a new dashboard. Going to select to be automatically sized and then it's going to have the map, the trend analysis at the bottom and the detail report on the far left, top left corner. Click on the map and choose this little filter icon. Then the co saved this dashboard. By the way, the trial license does not allow saving, but once you are saved. Now when meeting with the sales team using fingers on the tablet, the CEO can select different territories of the country and see how that affects the other views on this dashboard. Very interactive. It's pretty interactive, informative. Sometimes the CEO downloads data into Excel. So click a data point on the dashboard, select View data and then full data with all the columns. This is all the data behind that particular number and download open Excel. And now it has all the information here in Excel can be emailed and saved to other people. As final notes, we highly recommend a discussion with our team to understand your needs and plans. We can quickly evaluate how good a fit data self is and guide you through proven evaluation paths. For your information, we can usually get your data self system deployed in a couple of hours. Please contact us at this email and phone number. Thanks for watching.
