Count of Rows in a Chart

Once in a while someone asks me if there's a way to count the rows in a chart and put the number of rows in the chart title. I usually respond by asking, "why would anyone want to do that?" Usually, an expression that counts the distinct values of the fields used as the chart dimension is close enough. If you want a method that actually counts the rows in a straight table chart, here's something I have used that counts the rows and puts the count into a variable named chart_row_count. It will need to be modified for your environment and tested. (***make sure you also see the suggestion near the end)

Create a macro in the module code that looks like this:

Sub Count_Rows

'-------------

'/* Put your chart object id in the next line */

Set CHRT=ActiveDocument.GetSheetObject("CH70")

On error resume next

'/* in the next line, put an upper limit on the */

'/* loop bigger than your expected chart row count */

For i=1 to 500

ccontent=CHRT.GetCell(i,0).text

If Err.Number <> 0 then

Err.Clear

On error goto 0

ActiveDocument.Variables("chart_row_count").SetContent i-1, True

Exit For

End If

Next

On error goto 0

Set CHRT=nothing

End sub

The count in the variable includes the total line if there is one. You can use the variable value in a text expression in your chart title. One thing you must do that will vary from document to document depending on your requirements-- you must figure out how to trigger the macro when the number of rows in the chart might have changed. Triggering when a selection changes is easy to do. Triggering when a cycle group is changed will be harder. For testing, use a button to trigger the macro.

= = = = = = = = = = = = = = = = = = = = = = = = = = = = =

*** nrbhattad suggests this much simpler macro for finding row count:
set chart=ActiveDocument.GetSheetObject("CH01")

ActiveDocument.Variables("chart_row_count").SetContent chart.GetNoOfRows, true

.

Bookmarks for Dynamic Selection

I want to tell you about one of the cool things about doing a QlikView selection using a search expression. I’ve written about this topic before (Click here to read it). When you’ve made the selection with a search expression and then create a bookmark, the bookmark stores the expression and not just a static list of selected values. Then, the next time you click on that bookmark it re-applies the expression!

For example, if you click on a listbox of Salespersons and type =rank(sum(SlsHist))<=10 that will select the top ten Salespersons based on sales history (assuming that SlsHist is your field name for sales history). Then, create a bookmark. This will work best if the selection of Salespersons is the only selection currently made on the document. Now, every time you click on that bookmark it will select the top ten Salespersons. This works within the current set of selections, so, if you’ve already selected only products sold in Canada then when you click the bookmark it will select the top ten Salespersons of products sold in Canada.

You can export bookmarks, of course, and share them with others. I’ve started a small library of these search expression bookmarks that are useful for finding various data issues that need research. I just export the bookmark to a file whenever I create a new one that might be useful again someday.

If you click on one of these bookmarks and nothing gets selected then it means that nothing in the document satisfies the expression criteria.

Now, another cool thing: Beginning with QlikView version 9.0 you can create an Action that applies a bookmark. So, you could define an action that applies one of your search expression bookmarks and apply the Action to a Button or to the On Open event for the document. Using this technique you could make your document do a special selection whenever the document is opened (without writing any macros). Maybe you could make your executive dashboard show the ten most profitable customers over the past week whenever the document is opened.
.
Notes on how to write those expressions:

These are expressions that QlikView evaluates as either true or false. If you have a listbox of your company's Products and you type an equal sign, = , and then the expression; QlikView will evaluate the expression for each Product. If the result of the expression is true then the Product is selected, if the result is false then the product is not selected.

For example, you might type =count(distinct repair_orders)>100 on your listbox of Products and QlikView will select Products with more than 100 repair orders in the report data.
You might type =sum(invoice)<0 to find Products with invoices with negative amounts that need to be investigated. Or, on a listbox of hospital treatment dates type =sum(admissions)>sum(discharges) to find dates where more patients were admitted than sent home.

Mastering this technique for making selections will help make QlikView even more useful to you.

If you have any favorite or especially creative ways to use this technique please describe them in a blog comment (see below).

★ ★ ★

Fire a Trigger When User Changes an Input Field

Prior to the introduction of Input Fields there was no need for a trigger that would fire when loaded data changes. Because the only way that data could change was to reload it. But, when the QlikView document uses input fields then some of the data can be changed by the report user. The input fields feature allows data to be changed in list boxes, table boxes and some charts and can be very useful.

I helped advise a project that needed to know when data was changed by the report user because they had to apply custom logic for splitting the change from a summary level to detailed level. The solution was to use a variable containing an expression that looked something like this =Sum(sales_forecast) Then, a trigger was defined that would fire when the value of the variable changes. QlikView would evaluate the expression when the data changed and the trigger would fire causing a macro to execute which did the customized data split.

That solution worked well with QlikView 9.0 SR2 but when SR3 was installed on the computer then the trigger no longer fired. Without that trigger there is no way to run a macro when the data changes (if anyone knows of another way to fire the trigger when input field data changes please leave a comment below). The project is currently using 9.0 SR2 and everything works fine but they cannot upgrade to a later release unless this issue is solved for later releases.

[October 4, 2010 Note: The problem seems to be fixed in version 9.0 SR 6]

★ ★ ★