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!

* * *