Improving the sales analysis dashboard

Instead of spreading out our data to the point where we have information overload, perhaps we could remove a few objects and produce a better dashboard? I experimented by saving Executive Dashboard under a new name. Then, I made a copy of the Sales Analysis dashboard and started editing.

Note

To make a copy of a sheet, right-click on the sheet that you want to copy in a vacant area and choose Copy Sheet.

When I finished, my new sheet, Copy of Sales Analysis, looked like the following screenshot:

Improving the sales analysis dashboard

Figure 5-7: The new version of Sales Analysis sheet of the Executive dashboard

I chose to move the Customer Profitability table to its own tab because, although it is related to Sales, it is really a separate investigation and thought process. Either graph is acceptable, but I chose to keep Rolling Average Sales to Margin since it does show the year and month for the data represented. I moved the multi box group to the side, where the titles are not hidden. If we wanted to filter by those same items, we could put the cyclic group back into the straight table and get rid of the multi boxes. However, multi boxes do tell us whether we have a specific filter applied and take advantage of the relationship features of QlikView to tell us which other items are related.

Steps to create a new sales analysis tab

If you have not done so already, save a personal copy of Executive Dashboard.

Note

Save your Executive Dashboard as a new QlikView document with a different name using File | Save as… so that you can return to it at your convenience.

Perform the following steps to create a new Sales Analysis tab:

  1. Click on the File menu and then choose Save As…
  2. Save your new personal copy under a folder that you have access to and with a name you will remember. It will show up in the list of Recents when you start QlikView the next time.
  3. Now, copy the Sales Analysis tab. Right-click on a vacant area of the Sales Analysis sheet to bring up the context menu, and choose Copy Sheet …
  4. Now, start cleaning up the new sheet. Right-click on the Customer Profitability straight table and, from the pop-up context menu, choose Remove. Verify that you really want to remove the object. Do the same for the text box that has the informational title.
  5. Next, remove the container with multi boxes as we will recreate it in a different step and as it is taking up valuable screen real estate without being easily seen
    1. Right-click and choose Properties for the container. Make sure you have the CT09 container and not the new one you created. Remove all multi boxes from the container by moving them back to the existing objects with the Remove button.
    2. Click on OK to see the empty container.
    3. Now, right-click on the empty container and choose Remove.
    4. Verify that you want to remove the container.
    5. Click the Undo Layout button, or choose Undo Layout under the Edit menu, if you make a mistake.
  6. In order to move objects remaining on the sheet or resize them, we must enable the Allow Move/Size property. Start with the Line/Arrow object that was underneath the container that we deleted:
    1. Right-click on the Line/Arrow object and choose Properties.
    2. Navigate to the Layout tab of the wizard.
    3. Go to the Options section on the Layout tab (mid-right) and click on the Allow Move/Size checkbox.
    4. Click on Apply at the bottom of the wizard, and click on OK to close.
    5. Now, move the line up under the Year/Month/Revenue – Margin row.
    6. Repeat as needed to enable the moving of objects on the page.
    7. To lock an item in place, uncheck Allow Move/Size.
  7. Enable Move/Size on the Segment Change vs Revenue vs Last Year title and move it up under the word Month—just under the line you moved previously.
  8. Now, resize the title to two lines. Hover and use the white-filled double-ended arrow to resize text box.
  9. Next, enable Move/Size on the Layout tab for the straight table CH63—the one associated with the Segment title.
  10. While we are in the wizard for the straight table, let's change the table column headers. In the Multiline Setting section on the Presentation tab in the lower-right corner, check the box for Wrap Header Text, and leave the default to two lines. Click on OK.
  11. Click on OK again to exit the wizard, and then hover over the white space above and below the CH63 straight table. Stretch it under the title.
  12. Now, we are going to create the ability to switch our numbers in the straight table from ones to thousands and then to millions.

Creating a new variable

Perform the following steps to create a new variable:

  1. Go to the Settings menu at the top and choose Document Properties.
  2. Navigate to the Variables tab.
  3. Click on New to add a new variable. Name your new variable vNumDisplay.
  4. Under Settings for the selected variable section (in the lower-left corner) in the Value field, enter 1000 as the default value. Leave the rest of the defaults, and click on OK to exit the Settings wizard.

An interface for the new variable

  1. Create a new sheet object. Right-click to bring up the context menu and choose New Sheet Object…, and then choose Text Object.
  2. In the Text field on the General tab, type the word Millions.
  3. On the Actions tab, click on the Add button. Choose the External for Action type and choose Set Variable for Action.
  4. Click on the ellipses (…) next to the Variables box, and navigate to the Variables tab in the Edit Expression wizard that pops up. Choose our new vNumDisplay variable, and click on paste to put it into the Expression box.
  5. In the Value box, type 1000000 to set the value.
  6. Now, click on OK to exit the wizard. Size your new text object, move it above the table, and line it up below Dec.
  7. Repeat steps 1 through 6 to create new text objects for ones (enter 1 in the Value box), and move them under the word Aug. For thousands, enter 1000 in the Value box and move the new text objects under the word Oct.
  8. We have now created three button-type objects to interface with other objects on our sheet.

Note

If you have difficulty getting the ones box to activate, make sure it is not overlapped by the Segment title text object.

Applying the new variable

  1. Now we need to edit the expressions in our CH63 straight table to use text boxes to apply our variables:
    1. Right-click on the Straight Table and choose Properties.
    2. Navigate to the Expressions tab.
    3. Starting with the expression (the first one) $(vCurrentYear), click on the ellipses (…) next to Definition (right-hand side, third object from the bottom).
    4. Now, edit the expression by typing / to indicate that we are dividing.
    5. Then, go to the Variables tab in the Edit Expression wizard, and navigate to the new vNumDisplay variable. Click on Paste to paste our new variable onto the expression behind the forward slash (virgule). Our expression should now look like this:
      Sum({<[Fiscal Year]={$(vCurrentYear)}>}[Sales Amount])/vNumDisplay
    6. For the expression (the second one) $(vCurrentYear), click on the ellipses (…) next to Definition (the right-hand side, the third object from the bottom).
    7. Now, edit the expression by typing / to indicate we are dividing.
    8. Then, go to the Variables tab in the Edit Expression wizard, and navigate to our new variable vNumDisplay. Paste our new variable onto the expression behind the forward slash. Our expression should now look similar to this:
      Sum ([Budget Amount])/vNumDisplay
    9. Before we leave this expression, let's clarify in the table header that we are looking at the budget for the current year. In the Label box, just above Expression Definition, click on the ellipses (…) to bring up the Edit Expression wizard. In the Expression box, type Budget and then click on OK to close.
    10. Finally, let's add our variable to the prior year's expression. Click on the expression $(=(vCurrentYear)-1) to select it.
    11. Edit Definition by adding / and our variable vNumDisplay. Our expression should now look similar to this:
      Sum({<[Fiscal Year]={$(=(vCurrentYear)-1)}>}[Sales Amount])/vNumDisplay
  2. Click on OK to exit.

The straight table (CH63) now displays thousands as the default, but we cannot tell just by looking what precision is being displayed in our table—ones, thousands, or millions. To change that, edit the text box (TX180) title that we moved up earlier. Add a space after the word in and before the final apostrophe. Then, add an ampersand (&) and a space. Type in the name of our variable and another space followed by another ampersand. Finally, type in an apostrophe followed by an s and then another apostrophe. The expression will look similar to this:

='Segment Change in' & chr(10) & 'Revenue vs Last Year in ' & vNumDisplay & 's'

Now, when we click on our boxes, the title will change to indicate the precision of our numbers:

Applying the new variable

Figure 5-8: Buttons with thousands as the default and showing in the title

Adding a container with multi boxes

Right-click on a vacant area of the sheet, choose New Sheet Object , and then choose Container. To add a container with multi boxes, perform the following steps:

  1. Add multi boxes MB01, MB02, MB03, MB04, MB05, MB09, and MB10 to the container on the General tab.
  2. On the Presentation tab, set Container Type to Grid.
  3. Now, set Columns to 1 and Rows to 7.
  4. Size the container into approximately a 2-inch by 2-inch square.
  5. Edit each multi box in the container and reduce the font size to 8.
  6. Move the container to the left-hand side of the straight table and size the container to fit.
  7. Next, resize the left-hand side and right-hand side of the multi box selection display bar by hovering until you get a black cross (the cross is a double-ended arrow). When it is used on the left-hand side, we can move the rectangular box closer to the title; when it is used on the right-hand side, we can reduce the width of the rectangular box.
  8. Now, adjust the size of container again for the best display.
    Adding a container with multi boxes

    Figure 5-9: Container with Multi Box selection

Moving and changing the chart

To move and change the chart, perform the following steps:

  1. Edit the Line/Arrow that comes next so that you can move it up and then move it under the straight table.
  2. Now, edit the line graph so that it will allow you to move it and then move it under the line you moved previously.
  3. Right-click on the moved line graph and, on the General tab, check the checkbox labelled Use Title in Chart. In the box below that, type Rolling Average. On the same tab, change Chart Type to Combo Chart.
  4. Move to the Expressions tab. Make sure that you have Sales highlighted, and then change Display Option from Line to Bar. Click on OK to exit the wizard.

Finally, delete any other objects that you don't want on your sheet. If you want to investigate other possibilities on your own, add back objects in the container and move it into position on the left-hand side of the line graph. After the container is in place and sized, use the Properties wizard to insert the Age Profile Chart object.

To make the Ones, Thousands, and Millions boxes look more like buttons, try adding shadow borders.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset