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'

* * *

Using a Cycle Group in a Text Object

People usually use Cycle Groups in the definition of dimension for a chart. You can use cycle groups in other places if it helps with understanding your data. Here’s an example showing how you could use a cycle group with a text object to list out top performers at various levels in terms of sales--

First, define a cycle group with aggregation levels that make sense for aggregating sales. For this example I created a cycle group, named xgroup, and defined it as salesperson, sales_region, sales_district, product, and customer_group. The text object won’t provide a place to click on the cycle group and change the level, so I defined a small straight table chart using the cycle group, xgroup, as the dimension and one expression defined simply as 1. Then, I defined the number of rows to show zero rows so that only the totals row is visible and define the totals as sum-of-rows so that the number in the total is the number of distinct values from your cycle group.

You can use any expression that would be of value, but for this example we want to list the top five names in terms of total sales. If the cycle group was set to salesperson then it would list the top five salespersons in terms of total sales. Here’s the text expression I typed into the text object:

='Top 5 ' & GetCurrentField(xgroup)
& ' by Total Sales:
' & concat(
aggr( if(rank(sum(Sales))<=5,
$(=GetCurrentField(xgroup)) ), xgroup)
,', ' & chr(13))

The first line in the expression defines the first line that will appear in the text object and it is a title for the data. It uses the GetCurrentField function to get the current value of the cycle group and use it in the title.

The main part of the expression is an aggr function that will return an array of five values from aggregating the simple if statement over the values as defined by xgroup. I used a concat function to put a delimiter between the values of the array so it will show up in the text object. The delimiter will be comma followed by a space and then the chr(13) function is a carriage return or newline value to put each value on a new line. So, our finished text object might look something like this in the document:

Top 5 salesperson by Total Sales:
S.Hallas,
T.Mehta,
Z.Sui,
P.Esposito,
M.Naverra

Every time you click the cycle group icon on the little straight table chart, the text object changes to show results from a different level: sales_region, sales_district, product, or customer_group. If you place several of these text objects side by side then each could have a different expression. The expressions might be things like top 5 by count of sales orders, or top 5 in profitability, or total promotion budget, or total installation costs, or count of returned products, etc. The idea is to provide some extra information in the document using the flexibility of a cycle group and the simplicity of a text object.

The text objects are most useful and interesting, I think, when several of these expressions are used together in the same object along with text that explains, identifies, and provides meaning.

* * *

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.

* * *

Showing Values From a Parameter File With Set Analysis

I helped a coworker recently with a QlikView document that needed to show data from a parameter file. This parameter file was organized like many files that store settings or configuration or set-up values or .ini data – there are two columns: a parameter name column and a parameter value column. It is a popular format for designers because the parameters can be easily changed and new parameters added just by adding a new row.

My coworker’s document has a chart expression that should show the paint color ordered with an automobile kit if a specific paint color is in the order, otherwise, if the Paint_Color field from the order is null, then a default paint color should be shown that comes from a parameter file.
The parameter files two columns are: param_name and param_value. The parameter that holds the default paint color for the automobile kit is named “DEFAULT_PAINT”; that's the value of the param_name column and the param_value column on the same row holds the actual default paint color.

We wanted a solution that didn’t require changing the document loadscript; something that could be done in the expression. We decided to use some Set Analysis syntax because the normal QlikView associative connections wouldn’t easily let us show the param_value for the default paint color. The Set Analysis syntax would be used within a function to simulate the selection of the param_name field value “DEFAULT_PAINT”. Here is the expression we used:

If(IsNull(Paint_Color), Only( {$<param_name={'DEFAULT_PAINT'}>} param_value), Paint_Color)

We used the Only function because it allows Set Analysis syntax within its arguments but the If statement, by itself, does not.

I know Set Analysis syntax is complicated but the basic idea combines the concept of sets that you learned in high school math class with the QlikView concept of selection which you are already familiar with from using QlikView. It is a powerful technique worth taking the time to understand.

QlikView Ninjas reading this blog posting may see a resemblance between the parameter file I described and a mapping table in a loadscript. Indeed, if you keep the table around after the loadscript finishes (mapping tables are automatically deleted at the end of the loadscript), then the technique in this posting may be used to achieve something like mapping a value from a mapping table -- but doing it in a chart expression!

* * *

Expression Overview - Find/Replace

The desktop QlikView Expression Overview is a useful tool for searching expressions in a document or doing a Find/Replace on terms and values in the expressions (from the desktop menu Settings->Expression Overview). Many people put expressions or parts of expressions into document variables mainly so that they have only one thing to change when the expression needs to change instead of changing individual expressions in multiple places in a document. With the Find/Replace functionality you can accomplish the same thing: make the change in only one place and effect the changes wherever needed throughout the document.

If you use text expressions for column labels and object titles then the Find/Replace function can change those too. Find/Replace will also change text in the expression comments; something I find useful since comments often refer to field names and function names. Some people put a little label or tag in a comment line in the expression to help them find those specific expressions later. For example, you might put a comment in the expression that looks like this, /* Tushar, please review this */ to help a quality review person find the expressions that need to be reviewed. (To make a comment in the expression either put /* before the comment and */ after the comment, or, use // to indicate that the remainder of the line is a comment.)

One thing to remember is that the Find or Find/Replace will not find hidden objects. When I am using Find/Replace functionality on a document with hidden objects I usually change the document properties Security tab and click on Show All Sheets and Objects. Then, Find/Replace can make a change to all expressions, even those on the hidden objects. After making the change, update the Security tab again to uncheck the Show All Sheets and Objects box.

If you want Find/Replace to avoid changing expressions in a particular object then you can temporarily hide the object while making the Find/Replace changes. To temporarily hide an object go the object properties and type 0 into the Show Condition (type a zero character). After the Find/Replace changes are finished, go to Document Properties, Security tab and click the Show All Sheets and Objects check box. Then, find your object and clear the Show Condition box in object properties. Go back to the Security tab and uncheck Show All Sheets and Objects and your temporarily hidden objects are visible again.

* * *

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

.

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

* * *