Function With Flexible, Choosable Dimensions

I worked on a document last week that calculated a performance indicator metric based on budget amounts compared to actual spending amounts. The chart expression for the calculation used the Aggr function and two dimensions: spending program and geographic region. Simplifying from the actual expression, it looked something like this:

Sum( Aggr( Fabs( Sum(spendqty) – Sum(budgetqty) ), SProgram, SRegion))

The business requirement had changed and I needed to provide a method so the person using the document could choose the level at which to calculate the differences and the level could involve one or two different fields and a choice of weekly or monthly time period.

I decided to use a method that would eliminate the need for if statements in the expression and also provide an easy way to add more choices in the future. First, I built an inline load of the choices in the loadscript (a data island since the field names do not appear in any other table):

CALC_LEVEL:
Load * Inline
[CALCLVL_DESC : CALCLVL_CODE
Program, Region, Weekly : SProgram, SRegion, WEEK_start_date
Program National, Weekly : SProgram, WEEK_start_date
Region, Weekly : SRegion, WEEK_start_date
Program, Region, Monthly : SProgram, SRegion, Fiscal_period445
Program National, Monthly : SProgram, Fiscal_period445
Region, Monthly : SRegion, Fiscal_period445
] (delimiter is ':');

Note that I specified a colon as the field delimiter so that I could include commas in the field values of CALCLVL_DESC.

In the document, I created a Listbox for the CALCLVL_DESC field and titled it “Please choose a calculation level:” So, the business user can see the possible choices using familiar words that make sense in a business context. The listbox was configured for Always One Selected Value. When the user makes a selection of CALCLVL_DESC it means that there is only one available value of CALCLVL_CODE which is important for making the expression work correctly.

The dimension part of the Aggr function was changed so that the expression now looks like this:

Sum( Aggr( Fabs( Sum(spendqty) – Sum(budgetqty) ), $(=CALCLVL_CODE) ))

That makes the dimension part of the Aggr function take on the specific field names listed in the CALCLVL_CODE value. It allows the user’s choice to automatically control the fields used in the dimension. Adding new choices in the future would be a matter of adding new lines to the inline load.

This is a powerful technique, it is not limited to the Aggr function or an inline load data island. It can replace an entire expression or any part of an expression based on the current selection (as you read that sentence, keep in mind how flexible and powerful the concept of selection is for QlikView).   

★★★

Selecting Listbox Values Containing Either of Two Different Pieces of Text

Last week a coworker had a document loaded with data from a database job log table. It was over a million rows of log data that various batch jobs had written into. She wanted to select the rows that represented the start time of jobs and also the rows that represented the end time of jobs. Either one by itself would be easy. We knew that the LOGMESSAGE column contains the text ‘JOB START [‘ for log messages posted by jobs when they start up and the column contains the text ‘FINISH STATUS [‘ for messages posted by jobs when they end. To select either of those types of rows you would just click a listbox for the LOGMESSAGE column and start typing the text and QlikView would automatically select the rows. But, selecting both start message and end messages – that is different, how to do that?

The answer is to use an advanced search expression. For this example, you click on the LOGMESSAGE listbox and then start typing this:  =wildmatch(LOGMESSAGE,' *JOB START [*','*FINISH STATUS [*')

As soon as you type the equal sign, QlikView knows you are entering an advanced search expression. QlikView then tests the WildMatch function for each possible value of LOGMESSAGE and if the function is true then the column value is shown in the listbox and when you click [enter] the values are selected. It worked great for our log data problem- all of the start and end log messages were selected and shown in the chart along with their job start timestamps and job end timestamps.

The asterisks in the WildMatch expression are wild card characters that represent any series of characters. We could have added other wildcard matching strings too if we needed them. For example, if the job end messages had two different text layouts then we could have used the matching text to select both types of end messages. Despite the fact that the WildMatch expression contains the column name, LOGMESSAGE, it could be used with any of the columns or fields in the document and it would select the values that are associated with the LOGMESSAGE start and end messages.

★★★

Always One Selected Value Really

I developed a document a few months ago that provided the user the ability to select which currency (Dollars, Euros, Rupees, etc) to use for presenting financial data. The expressions in the document did not work correctly unless one and only one currency was selected. Originally, I clicked on the Always One Selected Value option available in Listbox and Multibox properties. But there was a problem with the option sometimes turning itself off and the first indication of a problem is when users noticed glaringly incorrect financial quantities!

The problem with the Always One Selected Value option is that it can turn itself off when someone does a Reduce Data operation on the document. My team frequently does that to reduce document file size in order to email a document or store it in the version control system.

Here's an alternative way to accomplish the same thing. In my document, I wanted a single value from the CURRENCY field always selected. So, I set up an action to be executed based on the OnAnySelect or OnOpen events. Go to Settings-Document Properties and click on the Triggers tab. In the Document Event Triggers window click on OnAnySelect. In the Field Event Triggers window, scroll down to CURRENCY (use your field name, of course), click on it and then click the OnSelect Edit Action(s) button.

When the Actions dialog box opens, click the Add button, add a Select In Field action, type CURRENCY (use your field name) into the Field box, and then type this into Search String:

=if(GetSelectedCount(CURRENCY)=1,CURRENCY,'Euro')

That expression makes sure only one value is selected and, if it isn't, it automatically chooses a default value - in this case it chooses Euro as a default currency value. You could also use a variable or an expression for the default value.


Click OK and then do the same thing for the OnOpen event. If needed for your situation you may also want to do the same thing for the OnPostReload event. Now, the document will always have one selected value for the field and if anyone tries to deselect it or select multiple values the trigger will switch to the default selection. For my document, I also checked off the Always One Selected Value checkbox in the Listbox Presentation tab.

[Author's Note 1Mar2013
Based on a reader comment, here's a slightly different expression that will return the selection to the previous valid selection instead of a default value:

=if(GetSelectedCount(CURRENCY)=1,UOM_SELECTION,if(isnull(Only({$1}CURRENCY)),'Euro',Only({$1}CURRENCY)))

It uses set analysis syntax to get the previous selection and only uses the default if the previous selection is null. 

List Box to Select Values From Two Fields at Once

I posted this answer on QlikCommunity yesterday and several people remarked on it so I thought I’d repeat it here.

I recently had a QlikView document that showed lagged sales forecast data. There were two date fields used in the data: a Create_Date indicating when the sales forecast was created, and, a Fcst_Date indicating which week was being forecasted. For example, a forecast of sales for the week of June 21st might be created two weeks earlier on June 7th. Then the Create_Date would be 07JUN2012 and the Fcst_Date would be 21JUN2012.

In the document, in addition to other list boxes and a multibox, I added a list box that allows a user to view and select values from both date fields at the same time.
I created a new List Box and in the Properties General tab, I didn’t select a field, instead I chose <expression> and typed this into the expression:
='Create date=' & Create_Date & ' and ' & 'forecasted week=' & Fcst_Date
I made the title of the list box “Select Create and Forecasted Dates”.
Now, I had a list box that showed the pairs of dates that occur in the data. The values in the list box can be green when they are selected or white when they are possible values and gray when they are excluded values just like any other list box. When I click on a value in the list box it selects the corresponding values from each of the two date fields at the same time.

One additional refinement was to make the values sort the way I want them. I opened the Properties of the list box and chose the Sort tab and checked the Expression box and then in the expression box I typed just the field name, Create_Date

This idea would work with any pair (or trio) of fields where being able to view the field values together and select the values together makes sense.


  *  *  *

Using Cycle Group in a List Box Expression

Here are a few more ideas from the Directed Creativity exercise in finding other ways to use Cycle Groups.

You can use expressions in a list box such that only field values satisfying a condition are shown. I’ve written about that before and it has been quite useful (See QlikView Maven from 1Sept2010). Today, we’re looking at expressions that contain a cycle group. This will allow a list box to initially show a list of products with high sales, for example. Then, after clicking on the cycle group icon, the same list box will be showing a list of customers with high sales. The report user need only select values from the list box or right-click and select-all to select the things satisfying the special conditions.

As in last week’s blog posting, I created a cycle group named xgroup containing field names related to sales: product name, sales person, price group, customer, and shipping facility. A typical expression I might use in a list box is to show the top 15 products by sales quantity. Using the cycle group I can define the list box with an expression that easily clicks through the top 15 products, or top 15 sales persons, or top 15 price groups, or top 15 customers, or top 15 shipping facilities.

The values shown in the list box are affected by selection, so, for example, if I start by selecting beverage sales only, then my list box values for the top 15 things is limited to the top 15 things within beverage sales.

Here’s what that kind of expression looks like:

=aggr( if(rank(sum(SALES_QUANTITY),4)<=15, $(=GetCurrentField(xgroup)) ), xgroup)

The title for the list box needs to use the current cycle group choice to make it clear what kind of things are shown in the list box. For my example, the title is a text expression that looks like this:

='Top 15 '&GetCurrentField(xgroup)&'s Based on Sales Quantity'

I can make a series of list boxes like that, each with an expression that shows me something important about the sales information in my document. Or, better yet, use the various expressions as choices in a Multi Box which takes up less room on the screen (although, the list boxes have the advantage of showing the values all the time—as you click through the cycle group you can see the list box values all change as the various cycle group fields are chosen). With simple changes to each of the expressions, my multi box might cover:

• Top 15 things by budget accuracy



• Top 15 things by product failure measurement



• Top 15 things by weekly shipment variability



• Top 15 things by admin and sales cost



• Top 15 things by customer feedback



• Things where sales exceeded 190% of budget

And so on – just think of the questions you have asked of your data in the past.

You cannot change cycle group choices through a list box or multibox. I have at times included a small chart with the cycle group as the dimension and a single expression just to give the report user a place to click on the cycle group icon. The expression consists of a single character: 1. The expression column totals are set to sum-of-rows and the chart is configured to limit the number of rows so that only the totals row is showing. The total for the one expression then is a count of the cycle group things. When the cycle group choice is customer then the expression total is a count of distinct customers. I use cycle group in the expression title too:

=GetCurrentField(xgroup)&' Count'

* * *

Cool Idea for Simplifying Chart Expressions

I have an inventory reporting document that shows inventory quantities at the end of each month. The basic unit-of measure for inventory is “cases” but the material master table contains conversion factors for each product to convert quantities in cases into other units of measure like kilograms, pounds, pallets, or inventory cost in dollars.

Similar to what I’ve done in the past to help a user select date formats, or currency, or language; I set up a small inline table in the document loadscript that looks like this:

UOM_SELECTION:
Load * inline [
UOM_SELECTION, Abbrev, ConvFactor
Cases, CS, 1
Kilograms, KG, CONV1
Pounds, LB, CONV2
Pallets, PT, CONV3
US Dollars, USD, CONV4
];

In the document, the field UOM_SELECTION appears in a multibox and the user selects the unit of measure they want to see used in charts and calculations. The field is configured for “Always One Selected Value”. When the user has made a selection then the Abbrev or UOM_SELECTION fields can be used in titles or labels since there is only one possible value for those fields.

The ConvFactor field is interesting – it is not really a conversion factor in the little table, it is the field name of a field that appears in the material master. For example, each material in the material master has a CONV1 field whose value is the conversion factor to convert cases of that material to kilograms. In the chart expressions where the inventory quantity must be shown, the unit of measure is handled, not by a series of IF statements, but with the name of the conversion factor field. A typical expression would look like this:
Sum( cs_quantity * $(=ConvFactor) )

The ConvFactor is evaluated and the value, which is a field name, is used in the expression. In the inline table, the value of ConvFactor for cases is simply a 1 since no real conversion is necessary and multiplying by 1 works fine. You are not limited to a single field name in the inline table-- you could use something like CONV5/CONV6 or 1/CONV5 and it would be evaluated in the chart expression just like a single field name. The chart expression is faster and simpler without the IF statements that would otherwise be required to determine which unit-of-measure was selected.

* * *

Select Top 4 Values On Open

I was working on a document that contained a lot of data and the performance when the document was opened or when the user moved from tab to tab was a little slow. On my own laptop when I save documents like that I usually make a selection of a small amount of data so that later when I open the document again it will open quickly. I wanted to do something like that for the users -- some kind of selection that would happen automatically when the document is opened, but it had to be a selection of data that would be useful and easy for the users to understand.

The data in the document was organized by a date field named POSTDATE so I decided that I would like the document to automatically select the most recent four POSTDATE values when it was opened. Most users look in the document for the most recent data and anyone who wanted to review older data could easily add older dates to the selection through the multibox.

The method I used is to create a trigger that would fire when the document is opened. The trigger would do a Pareto Select. Now, I'm not saying that this is the best way to accomplish the selection and it certainly is not the only way, but it was easy and didn't involve writing macro code.

Pareto Select is used to select a percentage of values in a field based on ranking of a value or expression. It is commonly used select things like the top 20% best selling products. In this case I wanted to use it to select not a fixed percentage but the four most recent POSTDATE date values. This is what I did:

From the menu choose Settings->Document Properties and choose the Triggers tab.
In the document event triggers click the Add Action button for the OnOpen event.
Click Add Action and in the Add Action dialog choose Action Type=Selection and Action=Pareto Select then click OK.
In the Actions dialog type POSTDATE into both the Field and Selection boxes. POSTDATE is not only the field I want to select but also the expression value I want ranked.
Then, in the Percentage box enter =(4/COUNT(DISTINCT POSTDATE))*100
And click OK. Save the document and the next time it is opened it will automatically select the four most recent POSTDATE values.

A similar method could be used to do automatic selection of any number of top values of any field. Pareto Select does its selection based on the current Possible values. If you wanted to ignore the current selection and select a percentage of all values then you should add an extra action to the trigger to clear the field before making the Pareto Selection.

Testing trigger actions is most easily done by defining the same actions for a button and then you can test the action by clicking the button. When you're done testing, remove the button and make sure the actions are associated with the proper event.

= = = = = = = = = = = = = = = = = = = = = = = = = =
Author's Note 6Dec2011:
This method didn't work the way I wanted when the document already included selections on the POSTDATE field. I couldn't find any good way to deal with that using the available trigger actions. I eventually replaced the Pareto Select action with a macro action that selects the four most recent POSTDATE values. The macro code looks like this:
sub Top_4_POSTDATE
set fd=ActiveDocument.GetField("POSTDATE")
fd.Clear
fd.TopSelect "=if(aggr({1} rank(POSTDATE),POSTDATE)<=4,POSTDATE)",4
set fd=nothing
end sub

* * *

Replace IF in Expression with Set Analysis

We often come across chart expressions that look like this:

=sum(if(SLSTYPE='DOMESTIC',SLSTOT))

It is easy to understand and if your document is a normal size and working well then leave the expression alone. But, if the document is large and you are looking for ways to speed up chart performance then consider changing the expression by replacing the "if" in the expression with the much faster set analysis syntax. Remember that set analysis is related to the concept of QlikView selections. Set analysis syntax works by specifying temporary selections within the function. So, if we wanted to consider set analysis for our example expression we should imagine whether the expression would work ok if we removed the "if" from the expression and, instead, made a selection on the SLSTYPE field to select the value DOMESTIC. If the expression would work ok in that situation then we can change the expression to use set analysis like this:

=sum({$<SLSTYPE ={'DOMESTIC'}>} SLSTOT)

We couldn't do that if the SLSTYPE field wasn't associated with SLSTOT because selecting the value DOMESTIC would have no effect on the expression. For example, if SLSTYPE had been defined in the loadscript as a Data Island unrelated to any other data then the expression wouldn't work correctly not matter how you coded the set analysis syntax within the sum function.

Another important consideration is whether or not the report is designed for users to make their own selections on the SLSTYPE field. If users are making SLSTYPE selections then they may not like an expression that overrides their selection within the expression. If the document allows user selection on the SLSTYPE field then we should define a separate field in the loadscript that will contain the exact same values as SLSTYPE and be part of the same QlikView table and same rows. Maybe call it SLSTYPE_SA and then we can code the set analysis part of our expression using the SLSTYPE_SA field and the expression will work correctly while still respecting all other user selections.

* * *

Looking Inside a Bookmark Without Opening It

This blog post comes from a question asked on the QlikCommunity forum. The question was how do you look at the selections inside a bookmark without actually selecting and activating the bookmark?

I had never thought about how you might do that. It is not difficult to list out the possible values for a single field based on a bookmark whether the bookmark has been opened or not. For example, for a bookmark named Feb_Sales and a field named ITEM you can put a text expression like this into a text object or chart title:

=concat({Feb_Sales} distinct ITEM, ', ')
That will give you a list of the ITEM values separated by commas. Note that it is a list of Possible values, it will only be the Selected values if the bookmark includes a selection on that particular field.

This technique can be used to give the user a peek into the selected or possible field values in the bookmarks; perhaps helping a user decide which bookmark should be used.

You can also apply the selection to the ITEM field without opening the Feb_Sales bookmark. Go to a listbox or multibox entry for ITEM and type this:

=count({Feb_Sales} 1 )

and that will select the ITEM values (if none of the ITEM values exists in your current document then it will appear that all values have been selected).

If there is a field normally associated with ITEM then you can select that. For example, if the field Product_Category is normally associated with ITEM then go to a listbox or multibox entry for Product_Category and type that same bit of text and it will automatically select the Product_Category values associated with the Feb_Sales bookmark without opening the bookmark!

* * *

Easy Text Search

People like the feature in QlikView that lets you start typing a word or phrase on top of a listbox (or the pull-down for a particular field on a multibox) and QlikView automatically shows you a list of the fields that contain that word or phrase. Notice that when you begin typing, a small box appears that contains the word you typed in between two asterisks. For example, if you click on the listbox for product names and start typing the word “cheese” a box appears containing *cheese*
The feature is called a text search and the asterisks are wildcard characters. The asterisk represents any string of characters… so the list of values that QlikView is showing you in our example is any group of characters, followed by “cheese”, and followed again by any group of characters… which really means any value containing the letters “cheese”. If you wanted only product names that begin with the word cheese then remove that first asterisk (so that the box contains cheese* ) and you will see only product names beginning with the word “cheese”. Similarly, remove the second asterisk and you only see product names that end with the word “cheese”.

Another wildcard character you can use is a question mark, “?”. The question mark represents any single character. I use it all the time when the field is a date field in the form of mm/dd/yyyy… then , for example, I can type something like 10/??/2010 and it will show me a list of all of the date values in October 2010 (“10/01/2010”,”10/02/2010”, etc.)

(Author's note: Nov 4, 2010. With QlikView version 10 the default behavior of the listbox is not to automatically insert wildcard asterisks as you start to type text. But, the search behavior is very similar and you can still type your own wildcard characters as needed.)

★ ★ ★

Selecting Null Values

It’s true that you cannot select the null values of a field. But, you can usually do something that will work even better to identify the null values. The idea is that you select values of a different field that are associated with the null values you’re interested in. As always, to help communicate the idea, here’s an example:

Imagine that you have a report of sales data but there’s a defect in the data and some of the products don’t have a product description. You can’t make a selection on missing or null values in the product description field, named PROD_DESC for our example, but you can easily select the products that have a missing or null product description. Go to the listbox for the PRODUCT_ID field or click the pull-down arrow for PRODUCT_ID from a multibox. Now, start typing this expression:

=isnull(PROD_DESC)

click Enter to finish the selection and you now have a selection consisting only of products that are missing their descriptions.

You can use a slightly different expression to select products with a blank or null product description; type this instead =isnull(trim(PROD_DESC))

This technique is useful if you use QlikView to find data quality issues or to validate data from suppliers or customers.

★ ★ ★

Random Sample Selection

Here’s an idea that will certainly come in handy some day. You can make a selection of random values from any field in your QlikView document. For example, imagine that you have a document loaded with your company’s employee expense report data for the past five years. And, imagine that you want to select a random twenty percent of the employees. Just click on a listbox of employee names or click on a pulldown arrow of employee names on a multibox. Then, type this expression:
=rand()<=0.20

And you will be looking at a random selection of approximately twenty percent of the company employees. Hit Enter to complete the selection.

This idea can work with any field in the document. You can also use an advanced search dialog box instead of a listbox or multibox. This kind of search expression works within the existing selections – if you had already selected only the part of your company operating in Mexico then your expression will select a random twenty percent of employees in Mexico. If you already have some employee names selected then you might want to clear the selection before using the expression. And, like I wrote in my previous blog posting, you can save an expression like this in a bookmark.

Note: If you are going to be doing this random selection several times on the same document then it may give you the same group of values each time. To make sure you get a different set of values every time you use the expression type this slightly longer expression instead:

=rand()<=0.2+now()*0

(See the comments section below for a suggestion about how to do a random sample selection as the Calculated Dimension in a Chart)

★ ★ ★

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).

★ ★ ★

Listbox or Multibox for Group of Things That Satisfy a Condition

The expressions that I wrote about in my last blog post can also be used in a listbox or multibox. For example, the expression
=If(aggr(rank(sum(SHIPMENTS)),ITEM)<=15,ITEM,null()) which lists the 15 top ITEMs by shipment volume, can be put into a listbox where it will look like a regular listbox for the ITEM field except that it will only show the top 15 ITEMs. [in this example the field SHIPMENTS is the number of cases of product shipped and ITEM is the product or material name]

If you have never used an expression in a listbox before, then go to the listbox properties and open the General tab and scroll up to the very top of the list of fields, there you’ll see the choice for entering an expression.

One thing different here from the kind of listbox that lists a field is that this listbox with an expression will change as your selections change. For our example, it will list the top 15 frozen items when you have frozen products selected but when you change your selection to coffee products it will list the top 15 coffee items. Another difference is that you cannot change the listbox sort option to sort the list by State (meaning the state of whether a thing is selected or not) but you can make the listbox sort things by numeric or character value.

You can set up a multibox with several of these expressions. I have a report with a multibox that has several choices for someone who is interested in finding data problems. Similar to the expressions in the last blog posting, I have =If(sum(SHIPMENTS)<=0,ITEM,null()) that can show me ITEMs with no shipments and =If(sum(SHIPMENTS)<=0 and sum(FORECAST)>0,ITEM,null()) to show me a list of ITEMs with a sales forecast but no shipments, and =If(sum(PROMOTION)>0,ITEM,null()) to show me a list of ITEMs with special promotional forecasts.

So, this is another option for you if you or your report users if you regularly have to select a special group of things; the special groups can be defined in a listbox or multibox or as a calculated dimension as explained in the previous blog post. In my next posting I'll describe how to select a group of things that satisfy a condition using a bookmark.

★ ★ ★

Calculated Dimensions to Show Things Satisfying a Condition

We often use a Cycle Group for the dimension on our charts. That allows the report user to switch between different dimensions easily. I’ve found it useful to add a few extra calculated dimensions that only show a particular group of things – a group that is useful to the report user or a group that requires attention.
For example, I often include a calculated dimension that only shows the top 15 items by shipment volume (it could just as easily be the top fifteen salespersons, or top 15 most requested service kits, or whatever). The expression for a calculated dimension like that is:
=If(aggr(rank(sum(SHIPMENTS)),ITEM)<=15,ITEM,null())

You will need to check off the Suppress When Value Is Null button on the Dimension tab of the chart properties. If it is important to you for the totals in the chart to remain correct for the entire population then change the code slightly so that the dimension shows the top 15 items and one choice for all other items something like this:
=If(aggr(rank(sum(SHIPMENTS)),ITEM)<=15,ITEM, 'All Others')

With a chart dimension like that the chart changes as the report user changes the selection. When the selection is Country=FRANCE, for example, the chart shows the top 15 items sold in France, and if the selection is Product=CLOTHING then the chart changes to show the 15 clothing items based on shipment volume. If the report user wants to make a selection for the 15 items then that is as simple as dragging the cursor over the 15 items listed in the chart.

In my job we are always looking for things that adversely affect the quality of sales forecasts. One example of something like that are items on the forecast reports that have no shipments. Unless the item is a very new product it should have shipments. When there’s no shipments for a forecasted item that often is a clue that there is a data problem. A calculated dimension for items with no shipments might look like this:
=If(sum(SHIPMENTS)<=0,ITEM,null())

A more specific calculated dimension expression that looks for items with a forecast but no shipments might look like this:

=If(sum(SHIPMENTS)<=0 and sum(FORECAST)>0,ITEM,null())

Similar calculated dimensions might look for items with no sales orders or no bill-of-materials or missing pricing data, etc.

Consider using a calculated dimension when your report users often have to review data for a particular group of things. The calculated dimension may be able to easily isolate a group of things that satisfy a condition even when doing the same task as a selection is complex.

In my next blog post I'll describe how to show and select a group of things satisfying a condition with a listbox or multibox.

★ ★ ★

Select Top Ten ITEM Values

I've mentioned several times the kind of expression that you can enter into a listbox or multibox to make QlikView do more complex selections than just clicking on values. Those expressions that you begin with an equal sign, =, are actually advanced search expressions and they can be very useful. Here's one more that helps answer a question that I get many times:

If you want to select the top ten or top twenty things based on a specific criteria there is a way to do it. For example, if you have a product identifier named ITEM and you have a sales history quantity named SHist then you can select the top ten ITEM values based on the highest total sales like this - first click on the ITEM listbox to activate it or click the down arrow for ITEM on your multibox. Then start typing this expression:
=rank(sum(SHist))<=10 and hit enter and it will have selected the top ten ITEM values based on sales history.

I'll have more to say about showing and selecting a group of things that satisfy a condition in my blog postings in August and September.

★ ★ ★

Stepping a Selection Through Each Value of a Field

Two years ago I posted an entry in this blog describing a macro that would let you step through all of the values of a field. The macro would successively select each value of a field. Shortly after I posted that entry the need for it became obsolete. QlikView has a much better method for stepping through the values of a field and it’s much easier to use than a macro.

Choose a listbox for a field that has the values you want to step through. Select one of the values. Now, just click the Down Arrow on your keyboard. QlikView moves the selection down to the next value. Every time you press the down arrow the selection moves down to the next value. Try it now – it’s easy! You would use this technique while watching the graphs and charts on your QlikView document and see the changes in the graphs and charts as the selection changes. You can quickly step through dozens of different products, or sales regions, or whatever you’re interested in. When you use this technique to step through date values it becomes a way to animate the charts and show behavior of data over time. The up arrow on your keyboard moves the selection the other way.

This is a case where the listbox works better than a multibox. You can click the drop-down arrow for a field on a multibox that contains a selection and then click the down arrow and it will move the selection, but then the list of values closes and you have to click the pull-down arrow again to get it back.

Now, try selecting several values at once on the list box… and then click the down arrow. The group of selections all move down one value. Now, try pressing the Page Down key on your keyboard and the group of selections move down several values. If this was a field of dates and you had selected seven dates then pressing Page Down would move down an entire week. The values that you select don’t need to be contiguous values and the technique still works.
Sometimes you might need to lock some of the other selections in order to better control the values that you step through.

★ ★ ★

Activate Multiple Listboxes

When I'm teaching new users about QlikView I always demonstrate how you can click on a listbox to activate it and then start typing a few characters and the values of that field containing those characters are displayed and can be easily selected. I also show them how if you start typing an expression like =Sum(Sales)>10000 it can help display and select products (or regions or salespersons or whatever) with sales greater than 10,000.

Clicking on the particular listbox to activate it is a simple but important part of the process. It is possible to activate more than one listbox at a time. And, if you do that, and start typing characters then QlikView will display the values containing those characters for all of the active listbox objects. You might not be able to hit enter and do the selections for all of the listbox fields at once because they will often interfere with each other.

The way you would activate several listbox objects at once is to click your mouse on the screen and holding the mouse button drag a "box" around the objects you want to activate. Or, you can activate all objects on the screen by pressing Ctrl-A. This feature may be useful when you are selecting multiple field values to match how they are selected in a document bookmark without implementing the entire bookmark (see the previous blog posting from June 15).

★ ★ ★

Restoring the selection for a single field from a bookmark

When you create a QlikView Bookmark to save your current selections it saves all of the selections for all of the fields where you’ve made a selection in your document. Often, that’s the reason you’re saving the selections as a bookmark – because it’s a complex set of selections for several fields that would take a while to recreate the next time you need them.

Sometimes, it would be nice to restore the selection from a bookmark for just one field. For example, if you have a bookmark named Project_3 that was used to save a complex selection from your monthly sales report, you might want to use only the part of that bookmark that is a selection on the Ship_Date field.

The way you would do that is to click on a listbox in your document for the Ship_Date field or click on the Ship_Date row in a multibox to activate the object. Then, start typing, make sure you begin typing with an equal sign (so that QlikView knows that you are beginning to type an expression). Type this:
=sum( { Project_3 } 1 )

That expression when typed into the selection box for Ship_Date will select only the Ship_Date values that are saved within the Project_3 bookmark. An expression like that can also be used with the Advanced Selection dialog box.

Remember that bookmarks can be exported and imported into other documents. You can even import bookmarks into documents quite different from the original document where the bookmark was created; when you do that the bookmark selections will be applied only to the fields named exactly the same as in the original document.

★ ★ ★

Combining Bookmarks

I was called to help with a situation last week where one person, working on a pricing project, had analyzed some data and identified a group of materials that would be needed later. The materials were selected and a bookmark was defined.

There was a second person working on a seemingly unrelated marketing project, who also identified a group of materials, selected them, and defined a bookmark for the selection.
The problem was that there was a need to combine the materials selections from the two separate bookmarks. There’s no good way to really combine two entire bookmarks and create a third bookmark out of them. But, when you’re interested in the selections for a single field as the people in my example were interested in the materials field, then there is a technique for combining the selections.

To illustrate the technique, assume that the first bookmark is named BK1 and the second bookmark is named BK2. First, get both of the bookmarks into the same document. An easy way to do that is export the bookmarks from the documents where they were created and then import one or both of them into a second document. In the document containing both bookmarks you will need a listbox for the materials field or a multibox that contains the materials field.
Click on the materials listbox or on the little black triangle next to the materials on the multibox. Then, begin to type this expression:

=sum( { BK1 + BK2 } 1 )

As soon as you type the equal sign, QlikView knows that you are entering an expression. It looks like an expression you might use in a chart but we’re going to use it here to select materials. The syntax is a set analysis expression – it can be a little tricky with the parentheses and curly braces but it is worth the trouble when you need it. As you type the expression a number of materials will be listed with a white background – those represent a combination of all of the materials from both of the bookmarks. Finish the expression by hitting the Enter key and the materials are now selected and show up with a green background.

A similar expression could be used to combine the materials from a bookmark with the currently selected materials in a document. For that you would follow the instructions as above but type this expression:

=sum( { $ + BK1 } 1 )

If you wanted to select only the materials that appears in both bookmarks (the intersection), then type this expression:

=sum( { BK1 * BK2 } 1 )

And, finally, if you wanted to select the materials that are both in your current selection and in the bookmark then type this expression:

=sum( { $ * BK1 } 1 )

★ ★ ★