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:
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.
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).
★ ★ ★
1 comment:
Great post Tim. A very simple way to create powerful filters.
Post a Comment