The Trending dashboard is located on the left-hand side of the Sales Analysis dashboard. It consists of four quadrants of bar charts that can successfully be viewed all at once; the selection section matches the other dashboards. These, in general, are good choices.
Each of the four charts has a Drill-down group button that allows us to get to successive dimensions and layers of detail in a particular chart. And the charts all interact with each other, providing additional information about the category we are drilling into. For example, if we choose New Zealand in the lower-right corner Region Chart, we can see in the upper-left corner Segment Change Chart that we have a profit margin on Wholesale Liquor; with both New Zealand and Australia on the grid, however, we have a loss margin. We can also see in the Product Change Chart directly above the Region Chart that Baked Goods are only a small contributor in New Zealand and that Deli makes the largest Product contribution.
Clearing Selections to bring Australia back on the grid now shows Wholesale Liquor to be a loss leader in Australia for the upper-left corner Segment Change Chart. It also looks as if Jenny Davidson is Zone Manager with the loss in Liquor sales in the lower-left chart displaying Change in Margin related to Sales Manager/Rep. And it looks like Baked Goods seems to be a profit leader in Product Change in Margin; that is, until we try to drill down into the longest bar in the chart and discover that the line actually represents Fruit.
Confusing us further, if we select 2010, suddenly Segment Change flips and Wholesale Grocery becomes the loss leader and is in the top row, while Wholesale Liquor moves to the bottom row. Jenny Davidson had a much better year in 2010 for Wholesale Liquor, while Chris Parkin appears to have really missed his quota on Wholesale Groceries. And if we drill down further, we can see that John Greg is the salesperson who will soon be looking for a new job.
This set of charts is color-coordinated in lavender and light green. An unusual choice, perhaps it was made so that colors did not imply good or bad in the margin display. But if that was the reason, it would have been better to choose a single color since the bars extend from zero to the right-hand side or the left-hand side with positive or negative numbers respectively. These two particular hues are very poor for an executive with red-green color blindness as they would appear as almost identical shades of gray, losing any visual distinction based on color. Perhaps a blue and a yellow would have accomplished the same result without the potential display issue.
The colors used in the Trending dashboard are set via color formulas and then locked down so deeply that we cannot change them without destroying the layout. We will examine using a Theme to change colors in our QlikView document later in this chapter, but, right now, we can learn how to create a Color Formula.
Right-click on the upper-left quadrant chart, Segment Change in Margin, and choose Properties. Navigate to the Colors tab, and notice that the first color sample in the upper-left corner has a little formula symbol f(x). The following figure shows this:
Figure 6-5: Color sample with the formula symbol
Double-click on Color Sample to bring up a wizard for Color Area. In the Color Area wizard, you can see that the radio button for Calculated is selected and a formula is entered into the box with ellipses (…). Clicking on the ellipses brings up the following formula:
if((If(Measure='Margin', Sum({<[Fiscal Year] = {$(vCurrentYear)},FiscalMonthNum = {"<=$(vCurrentMonthNum)"}>}
[Sales Margin Amount]) - Sum({<[Fiscal Year] = {$(=(vCurrentYear) - 1)}, FiscalMonthNum = {"<=$(vCurrentMonthNum)"} > }[Sales Margin Amount]), Sum({<[Fiscal Year] = {$(vCurrentYear)},FiscalMonthNum = {"<=$(vCurrentMonthNum)"}>} [Sales Amount]) - Sum({<[Fiscal Year] =
{$( = (vCurrentYear)-1)}, FiscalMonthNum = {"<=$(vCurrentMonthNum)"}>} [Sales Amount])))>0,
RGB(102,204,102), RGB(255,0,0))The very last row of the formula RGB(0,255,255), RGB(0,0,255) assigns colors to the chart bars. RGB(102,204,102) is pale green and RGB(255,0,0) is red. We cannot affect changes to the color formula on the Trending tab because a theme has been applied to the sheet object that protects it from having changes made. We can, however, create our own sheet to experiment with colors and learn more.
To experiment with color formulas, create your own chart on your Copy of Sales Analysis tab or create a new tab.
- Create your new sheet:
- Go to the Layout menu and choose New Sheet.
- After the new sheet appears, right-click to bring up the context menu and choose Properties.
- Rename your new sheet
Color Testin the Title on the General tab. - Click on OK.
- Add your chart:
- Right-click on your sheet and choose the New Sheet object.
- Choose Chart.
- In the General tab, title your chart
Sales Color Testand, for Type, choose Bar Chart (the first one). - Click on Next.
- Scroll down and choose Segment Group for Used Dimension.
- Click on Next.
- In the Expression wizard, type in or select and paste this formula:
Sum ([Sales Margin Amount])
- Click on OK to close the Expression wizard.
- Label your measure Margin.
- Click on Next and Next again to get to the Style Wizard page.
- Choose the horizontal orientation instead of the vertical orientation.
- Click on Next to take the defaults for Presentation.
- Click on Next to take the defaults for Axes.
- On the Colors Wizard page, click on the first Data Appearance Color block to bring up the Color Area Wizard page.
- Switch the radio button to Calculated.
- Click on the ellipses to bring up the Expression wizard page, and enter the following formula:
If(Sum ([Sales Margin Amount])>0,RGB(0,255,255), RGB(0,0,255))
- Click on OK to close the Expression wizard; the color area changes to a bright turquoise blue and shows the formula symbol.
- Click on Next, Next, Next, and Finish or just click on Finish to accept defaults for the rest of the wizard.
- Now, add a list box to see your colors change:
- Right-click and choose the New Sheet Object from the context menu.
- Choose List Box.
- Choose the dimension Drill-Through Group Product Drill.
- Name your List Box
Product Drill. - Click on OK.
- Now, to see the effects of your formula, we have to find a negative Sales Margin sum. Thus, in your new Product Drill List Box:
Our amounts under the chart switch to negative numbers, and our bar changes to a medium-hued royal blue. Save your QlikView document under a new name or over your current document if you want to experiment on your own later:
Figure 6-6: Negative Sales margin
The color codes we used in our formula were actually Visual Basic Red, Blue, and Green designations. RGB(0,255,255) is yellow and RGB(0,0,255) is blue. For some undocumented reason, when they are used together in a formula for color in a horizontal bar chart, they blend together for the first color, so in Color Test Chart, we get bright turquoise. On the Trending tab, the pale green RGB(102,204,102) and the red RGB(255,0,0) blend to give us the lavender colored bars. Perhaps it works similar to the expression shortcuts where 1 allows us to go backwards and forwards, while $ in the top expression freezes. These are the color codes used in HTML to designate colors in a web page.
For additional help with color selection in the RGB Visual Basic code, please visit:
http://www.tayloredmktg.com/rgb/ or http://rapidtables.com/web/color/RGB_Color.htm
Another way to get the RGB numbers is to open Microsoft Paint and click on the Edit Colors button to pop up the Colors interface. After picking a color, you can see the numbers to use for Red, Green, and Blue. Also, Hue, Saturation, and Luminosity will be shown and can be used to modify the numbers and therefore the colors in QlikView objects.
We cannot see what theme is applied by opening the Sheet Properties wizard, and the only way to tell what is in Theme is to examine the XML code. Nonetheless, themes are very useful.
The QlikView Layout Theme is a set of formatting properties that can be applied to a whole QlikView layout or to specific documents or charts. Layout Theme files are stored in the QlikView theme folder, normally found under the Windows Application Data folder for the active user. Layout Theme files are coded in XML and can be manually edited in an XML editor but are easier for most of us to manage through the Theme Maker Wizard.
To examine the Theme Maker Wizard, right-click in a vacant area of the Trending sheet and bring up Sheet Properties. On the General tab, you will find a Theme Maker button that will allow you to create a new Theme, and the Apply Theme button that will allow you to switch between the theme you created and the existing one. The Theme Maker Wizard is located in the Layout tab for the objects on a sheet because you can have themes for any level of QlikView objects. For the Clear Selections button on the Sales Analysis tab, you will find the button for Theme Maker Wizard located on the Layout tab of the Properties Wizard.
Themes are useful in creating a consistent look throughout a QlikView document and in enforcing a company style so that all QlikView documents are branded in the company's approved colors, fonts, border, caption styles, and printer settings for printable object types. Try applying Themes that came with QlikView to Sales Color Test Chart. The following screenshot shows this themes available to apply:
Figure 6-7: Themes that came with QlikView
As you can see in the preceding screenshot, there are multiple general-purpose Themes provided with the QlikView installation package, but, if you want to create your own theme, follow these steps:
- Format a Qlikview document via Properties to your preferences.
- Format Sheet Properties of one sheet to your preferences.
- Then, create and format one sheet object of each type (List Box, Chart, Statistics Box, and Button) to your preferences. Caption/Border properties usually only need to be formatted once on one of the sheet objects.
- Now, open the Theme Maker Wizard from the Layout tab for the sheet, and create a new theme by inserting properties from the first of the formatted objects you created (the order does not matter).
- Run Theme Maker Wizard repeatedly—once for every remaining formatted object you created.
QlikView Layout Themes can be applied either manually, after a new QlikView Document is started, or automatically, every time a new QlikView object is created. Themes can be applied to individual sheet objects or to groups of sheet objects. Themes can also be applied to a whole sheet or an entire QlikView document.
To apply a theme to QlikView objects, follow these steps:
All Properties in the theme that are applicable to the document will now be applied, including all sheets and sheet objects that have formats in the theme applied.
The theme will be applied to all applicable objects on the selected sheet.
Theme Properties that are applicable to the selected sheet object will now be applied. To apply your theme to a group of sheet objects, you must first make them all active by holding down the Shift key and clicking each object or by lassoing around the selected objects.