Color Alerts and Calculated Colors In QlikView Scripting - QlikView

Dimension Limits

One of the simple requirements while comparing data is limiting the dimensional values on the chart. ‘Dimension limits’ are a feature that can be applied to any chart object, except the pivot table and the gauge chart. As a developer, limiting the dimensions that appear in the chart is helpful for allowing users to focus more on meaningful data rather getting distracted. Setting dimension limits can be done on the corresponding chart properties dialog box (the dimension limits tab), as seen in the following diagram:

Follow these steps to set up dimension limits:

1. Enable dimension limits by selecting the restrict which values are displayed using the first expression check box.
2. Determine which show option is desired:
* show only: select first, largest, or smallest from this dropdown, and set the number of values you wish to show in the chart. Note that first will display the data as set in the sort tab of the chart properties dialog. Largest and smallest return data in descending (largest to smallest) and ascending (smallest to largest) order based on the expression.

* show only values that are: select greater than or equal to, less than or equal to, greater than, or less than in the drop-down list. This filter applies to the next field, which allows you to set a value (either a percentage, exact value, or calculated value). This value can be compared as relative to the total or an exact amount. For example, you could set up the dimension limit to show as greater than or equal to 5% relative to the total for representing the top20 percent of the dimension. All other dimensions can be included in the others data field if the show others check box in options is selected. Note that if the show others check box is selected, it will decrease the number of named dimensions by 1 (so if you set 10 dimensions to show, only 9 dimensions would display, plus one labeled as others).

Related Page: QlikView Data Transformations In Flat Files

*show only values that accumulate to: choose the value percentage or actual value, in exact amounts or relative to the total, of the first (as defined in the sort tab), smallest, or largest values returned by the dimension. This is useful if you want to display the top percent of sales reps, for example.

*the global grouping mode and collapse inner dimensions options only apply if you have subsequent or inner dimensions, and determine whether you want the restrictions set in this tab to apply to the selected dimension, or if you do not want the inner dimension to display.

The value box controls how many items that a chart receives.

Color Alerts and Calculated Colors

Color alerts are also referred to as calculated colors, and they are useful for business users. Color alerts can be coded in the chart (or other) object and are commonly used to change the fonts or background colors of table cells, lines, and bars in the charts. Calculated colors can also be used for navigation, to show users what the current tab is:

Frequently Asked QlikView Interview Questions & Answers

if (Get Active Sheet Id () = “DocumentSH01”, $ (v Color 1), $ (v Color 2))

The preceding code sets colors as follows: if the sheet (tab) is named SH01, the color of the text or object is the color as set in the v color1 variable, and if the active sheet is not SH01, the text or object is the color as set in the variable v color 2.

If (get active sheet ID ()= “documentSH01”, color (1), color (2)

The preceding code does the same as the previous one, but sets the colors to either color1 or color2 on the document’s color palette (as seen in the colors tab in the chart object, for instance). Calculated colors can be useful in identifying top-selling products, above or below average sales, or other metrics.

Related Page: Qlikview Scripting Features And Functions

There are two main ways to set up color alerts: in the expressions tab of the chart dialog box, or in the visual cues tab in the straight table or pivot table properties dialog box.

Let’s try adding a color alert  in a couple of different ways, by following these steps:

1. load the following data into your script, save it, and reload the script.
Case count:

 LOAD*   INLINE [
Specialty, cases
Thoracic, 400
Orthopedic, 575
Endoscopy, 945
Trauma, 1132
Neurological,95
Sports, 1256
Gastroenterology, 222
Cystoscopy, 448
];

2. create a new bar chart by right-clicking on the context menu: new sheet object/chart, then selecting the bar chart icon in the properties dialog.

Related Pages: Rename Field and Qualify in Qlikview

3. in the dimensions tab, add specialty to the used dimensions section. In the expressions tab, add cases as an expression, then in the definition field, enter the sum (cases). Expand the definition cases expression by clicking on the plus sign, and click on the back ground color. In the definition field, enter:
          =if  (sum (cases) >=avg (distinct total cases), color(1), color  (2))
Or:
           =if (cases <600, color (1), color (2))

The first line of code tells qlikview to evaluate if the sum of cases is greater than or equal to the total of all cases (distinct here is optional). If it is, choose the first color, color (1), from the color palette in the colors tab in this object’s properties dialog box. If it is not greater than or equal to the average number of all cases, color the bars as the second color, color (2), in the colors tab. We could have also used a variable we set up for the color or the exact RGB values.

Another option, the second line of code, gives a fixed value for the average we want to set as a bench mark (600cases). If the sum of cases is below 600, use the first color in the colors tab and if it is larger, choose the second color in the colors tab.

4. in the sort tab, set the data to sort by y-value, ascending.
5. for this example, let’s set a reference line that shows the average number of cases performed per specialty. In the presentation tab, click on add in the reference lines area. In the expression field, enter Avg (cases), set the line formatting to 2 points, set it to the color red, enter a label name such as case average and show the label in the chart, and click on ok. click on apply and then on ok once again to show your new chart.
 

6. The work we have done here will also be applied if you transform the chart to a straight table. In this chart’s object properties, in the general tab, choose a straight table. in the sort tab, select to sort by numeric value, ascending. If you do not want the totals row to show in the table, go to the expressions tab in the total mode area and select no totals. Click on apply and then on Ok.

Check Out QlikView Tutorials