First, since we have just discovered it is possible, we will set up a Fast Change type for our Order Details straight table. Go back to the Order Details tab of the CFO dashboard. Start by right-clicking on the large straight table and choosing Properties. In the Properties wizard, navigate to the first tab, General. Here, in the bottom right-hand side, we can see the Fast Change group of controls. Choose Line Chart, Pivot Table, and Straight Table for the Allowed Types.
Now check the radio button in the section for Preferred Icon Position below the Allowed Types and next to In Chart instead of In Caption; click Apply and close the Properties Wizard. There will now be a small icon in the upper corner of the straight table that looks like a tiny line graph. If you click on it, the straight table changes to a pretty, but not necessarily informative, line graph, and the icon for the pivot table appears to the left of the new line graph.
Figure 8-4: Line graph with Pivot Table Fast Change icon on the left, beneath the title
Continue by clicking the icon to the left of the line graph to switch to the pivot table; when you are done reviewing that, click the icon on the right to return to the straight table display. Now cycle through again to the pivot table.
Right-click to bring up the context menu, and move down the list. You will see a couple of options near the bottom that you may not have paid much attention to before. With these options, you can Send to Excel… or Export… the data. If you cycle through the Fast Change to the Line Graph, you will get a slightly different choice: Send Values to Excel… in the context menu. If you want the line graph to be sent to Excel, you will need to choose Export. You will then export it as an image file and insert it into Excel, Word, PowerPoint, or other programs. You can also use the next option down, Copy to clipboard, and then paste the image. If you choose to Export the Values, or, from one of the other Fast Change screens, to Export to Excel, you get formatted data from the selections made at the time. Unfortunately, with the line graph on Sheet, QlikView sends the wrong column names to describe the data to Excel. You should be in the straight table to use Export to Excel in order to send the data to someone who does not have QlikView.
How do we tell QlikView to format our negative numbers in red so that they stand out, and how do we format accounting style numbers with parentheses? Right-click in the straight table again to bring up the Properties Wizard. At this point, if you want to, you can turn off the Fast Change function by unchecking the boxes under Fast Change in the General Tab. Next, navigate to the Visual Cues tab. Now click on either Sales Price, Revenue, or Margin in the Expressions Box and look down at the upper and lower boxes. You will see that the lower box has a zero in it telling Qlikview that the default is red if that value is less than or equal to zero. Now change the Normal text to a deep blue by clicking on the black rectangle to the right in the same row as the word Normal. Bring up the Color Wizard, choose your new blue color, and click OK. Make sure your new color shows in the box opposite Normal, and close Properties to view your changes.
To format our negative numbers, we use the Properties Wizard again. You may already be familiar with formatting numbers in Excel. If you are, this will be useful because QlikView, for the most part, uses the same format codes. On the Numbers tab in the Properties Wizard, you will see that the format for Revenue is currently given as follows:
$#,##0.00;-$#,##0.00
Change that to: $#,##0.00;($#,##0.00)in order to switch to an accounting format with parentheses.
Dynamically calculated expressions can be entered almost anywhere where you can enter text such as chart titles or headings. The time required for a dynamically calculated expression to evaluate and to display is dependent on its environment and the type of data that it is required to display. For example, dynamic expressions in charts and tables that are defined in the expressions dialog are only calculated when the sheet object is visible and when the data changes. Dynamic expressions are not calculated when the object is minimized.
On the other hand, if the sheet object title is dynamic, this expression is evaluated each time a change occurs. Some expressions are more resource-intensive than others, and become more so with an increase in the frequency of their evaluation. The time functions, Now() and Today(), will be evaluated whenever a recalculation is required. The Now() function can especially slow down the response from the rest of the application, because it causes a recalculation of the application every second.
We can test this by creating a text box in our Order Details tab. Right-click in the white area of the sheet to bring up the context menu, and choose New Sheet Object | Text Object.
Enter the following expression in the Text Object:
=If (Now(),second(NOW()) , 'RED')
Click OK to display the seconds counting through the text object. The word RED in the expression is a placeholder and will never be displayed.
Figure 8-5: New format for negative sales and a text object counting down the seconds with the Now() function