Straight Table with Different Expressions on Each Row

We had a requirement a few months back for a document that would show a table with different expressions on each row along with some subtotals and expressions that reference other rows and columns. It needed to be a single table that could be sent to Excel. It was obviously a requirement that grew out of existing, legacy reporting solutions using Excel but many corporations are wedded to Excel and the larger the corporation, the harder it is to find anyone who believes they have the authority to make changes or do something different.

The solution was to build a document with a straight table with a single dimension that is loaded in the loadscript with an inline load like this:

TBL_KPI_TYPE:

LOAD * INLINE [

KPI_NO, KPI_TYPE

1, Transportation:

2, Intermodal % of Miles

3, Sea %

4, LTL %

5, Avg Pallets/Truck

6,

7, Total Diesel Cost

8, Diesel $/Gallon

9, Diesel # of Gallons

10, Average distance to Customers

];

The straight table used KPI_TYPE as the dimension so those text values get listed in the leftmost column like any other dimension. The chart expression had to be slightly different for each column -- some columns were fiscal months, some fiscal quarters, one was a year-to-date column -- but they all looked something like this:

Pick(KPI_NO,

'  '.

$(v.intermodel_pcent_miles),

$(v.sea_pcent),

$(ltl_pcent),

$(avg_palletspertruck),

'    ',

$(total_diesel),

$(diesel_price),

$(diesel_tot_gallons),

$(avg_dist_to_cust)

)

This is simplified from the actual document.

• Note that the Pick function in the expression uses KPI_NO which is defined in the inline load. The Pick function avoids “if” statements that can affect speed performance for the table as users change their selections.

• The dimension isn’t a real data field. Each line in the expression must use the appropriate field names and set analysis to satisfy the requirement for the row. In the example above, variables are used for each row to help make the overall expression understandable at a glance.

• Because each row is a different expression, the formatting must be done within the expression in the variable using the Num function. Some of those rows are integers, some are in thousands, some are money and some are percentages.

• Because some of the expressions referred to other rows and columns (using the Above and RangeSum functions), I had to turn off the ability to drag and drop the columns. I also had to lock in the selection of all KPI_TYPE values so that the user wouldn’t accidentally make selections on KPI_TYPE. If the expressions were not using functions like Above then allowing the user to make selections on KPI_TYPE would make the chart more flexible.

• Note that some of the KPI_TYPE values can be labels and the corresponding row in the expression is just a blank line. (Make sure the chart properties are not going to hide a row of all blanks.)

• As Aaron mentions in a comment below, something similar can be achieved with a synthetic dimension using ValueList. I think ValueList is better suited to a small number of values but it offers the advantage that a user can't accidentally select one of the values by clicking on the chart; a downside is that it may consume more resources (versus an actual field dimension) when used with a large data model.

★★★

Chart Adjusts to Whatever Fieldnames are Loaded

I’m often called on to validate a new data file from a client or to examine a new data extract. My first step is to load data into a copy of Steve Dark’s Data Profiler (search on quickintelligence data profiler). I’ve made a few modifications to Steve’s original document – here are a couple of the most useful.

I added a line to the loadscript that uses preceding load to add a record number to the rows being loaded. My line is added between the table label and the first line of the regular Load statement. It looks like this:

DATAFILE:  //table label
Load *, recno() as zrecno;  //this is the preceding load line
Load   //this is the beginning of your "regular" Load statement

For the second set of modifications, I added a new sheet or tab which I named Table.
On the new tab, I added a multibox and then I added, not fieldnames, but 20 expressions. You can add any number you like but 20 seemed like plenty. These are mostly copy and paste so they are not a lot of typing and you can start by copying the expression shown in this blog and pasting it into your document. The first multibox expression looks like this:

[$(=only({1<$FieldNo={1}>} $Field))]

That expression evaluates to the fieldname corresponding to field #1. Then I add a second expression to the multibox:

[$(=only({1<$FieldNo={2}>} $Field))]

You can see that in the second expression, I changed $FieldNo={1} to $FieldNo={2} so that the second expression evaluates to the fieldname corresponding to field #2. I did that for each of the 20 expressions so that the 20th expression in the multibox contains $FieldNo={20}

Then, I added a straight table chart. I used zrecno as the dimension. Then, I added 20 expressions. For the first expression, I typed this:

[$(=only({1<$FieldNo={1}>} $Field))]

And for the label, I typed this:

$(=only({1<$FieldNo={1}>} [$Field]))

Again, the rest of the expressions are similar, each time changing the $FieldNo={1} to be 2 for the second expression and its label, $FieldNo={3} for the third expression and its label and so on for each of the 20 expressions. Click OK when you’re done.

Now, you have a multibox and a straight table chart that appear as though you typed in the 20 fieldnames. But, these objects will adjust themselves to whatever fieldnames are loaded. If you load a file with less than 20 fieldnames, the “extra” ones will just be null.

You can copy and paste these objects into other QlikView documents and they will adjust themselves to whatever the first 20 fieldnames are in the document (see important info in first blog comment below). But, I think these are most useful in a document where different fieldnames can be loaded each time.

I checked Dropdown Select for all columns in the Presentation tab to make it easier for the document user to make selections on the chart. 

In order that we don’t burn up all of the memory on the computer, I added a calculation condition to the straight table chart with this expression =if(count(zrecno) < 200000,1,0) so that the chart will refuse to appear if there are more than 200,000 rows. I customized the error message for the calculation condition to say, “There’s more than 200k rows. You have to make some selections to reduce the amount of data in this chart.” A calculation condition like this is a good idea whenever you have an object that might try to show more data than is possible with the available memory.

  ★★★ 

Easy to Add Multivariate Analysis Feature

Here is a technique that I’ve used several times over the past year to quickly add a multivariate analysis feature to a document. We have numerous documents that have a multibox for selections and usually a graph and at least one chart; usually a straight table with a cycle group for the dimension. I usually start with one of these existing documents.

This feature uses QlikView alternate states. I normally steer people away from using alternate states in documents used by business users as it can be confusing. But, in this technique, it seems to be easily used and understood.

The first step, is to add an alternate state to the document. Using the menu, choose Settings->Document Properties, then choose the General tab and click the Alternate States… button (if you don’t see the button then you may be using an older version that doesn’t have the Alternate States functionality). On the Alternate States dialog box that appears, click Add and then simply type a  B  and then click OK. This defines an alternate state in the document named B. It’s not a very descriptive name but it is perfect for this technique.

Now, we’re going to duplicate the multibox. Right-click on the multibox and choose Copy to Clipboard -> Object. Then, from the menu choose Edit->Paste. QlikView will paste the new copy right on top of the old one so you need to drag the copy of the multibox off to the right. This is easiest if the multibox properties Show Caption option is checked so you can click on the title bar at the top of the multibox and drag it. Make the caption of the original multibox say Selections:   and make the caption on the new copy of the multibox say  B Selections:   It may help to make the caption font on the new copy in italics.

Easy, so far? Now, right-click on the new copy multibox and choose Properties. Choose the General tab and look for the Alternate State field at the top. Click the little down arrow on the Alternate State box and click to choose B and then click OK. You now have two multiboxes: one that controls the usual selections and one that controls an entirely different set of selections in alternate state B.

Here's the part that makes it useful. In your charts, you probably have a column something like this:   Sum( Sales )  with the label Sales on it. Add a new column, label it Sales B, and put it right next to the original column. For the expression, type  Sum({B} Sales) 

The syntax is the same as for set analysis. But, this is simpler, just use the {B} inside the argument for any aggregation function and it makes the aggregation use the selections that are defined in your B Selections multibox. No need to change the chart dimensions or change anything in the load script.

Now, you can compare the column for Sales with the Sales B column with the numbers side by side. I assume the fields most useful for the data were already defined in the multibox. So, you can change the B selections to easily compare, for example, June sales next to March sales; or compare tier 1 customers versus tier 2 customers; or profit from the 1kg size versus the 500g size, or inventory turns at the Memphis warehouse versus turns at the combined California warehouses or total cost of the cheesy-beans business versus costs with administrative costs unselected,… etc.

As quickly as you can add a new field to the two multiboxes, you can have a side by side comparison of data for different selections on that field.

I’ve found that graphs that use this technique should use a similar but noticeably different appearance for the two expressions. Using the example from above, a graph of Sales could use a red line in the line graph and use a dotted red line for Sales B.

You can use color in the chart to point out the biggest differences between the regular aggregation and the B aggregation. I’ve sometimes used the Colormix wizard to color the rows with the biggest differences in bright yellow and the smaller differences with less bright shades of yellow (the Colormix wizard is described in the manual and it is well worth your time to become familiar with it).

It would be just as easy to also add C Selections but, so far, I haven’t found a case where it adds value.

  ★★★

Divide a Group of Dates into 2 Groups (so you can tell if the business measurement is getting better or worse)

Author's Note:  This is an interesting technique but I believe a simpler and more general purpose method is in a blog post from Aug 2016. Search on "QlikView Maven Is My KPI Getting Worse or Getting Better"


I did a weekend project several weeks ago where I was working with a database containing some business measurements taken each day over a long stretch of time. I wanted an easy way to visualize whether the aggregate measurements were getting better or worse over time. In order to satisfy the “easy” part of that objective, I decided to build expressions that would automatically split the available measurement dates into two groups:  the earliest half of the dates and the latest half of the dates. That would allow me to create charts with a heat map feature that easily identifies areas for further investigation.

I won’t complicate this story with details of the business measurements or with details of how to create heat map colors (for more info, search for “QlikView Colormap Wizard”). We’ll call the measurement sorg_delta and do a Sum of those values over the dates.

I made a simplifying assumption that I would always have a contiguous span of dates. So, calculating the median date would give me the middle date that splits the group of dates into the two half groups that I need. Using set analysis syntax to make the sum function operate over the early half of the dates looks like this:

 Sum({<MDate={'<=$(=Date(median(MDate)))'}>} sorg_delta)

But, I can’t do something a simple as that for the other half of the dates because I may be working with an odd number of days and any comparison of the data must be done for the same number of measurements otherwise it would affect a comparison of the aggregate business measurement between the two groups. So, the other half of the expression that includes the later half of the dates looks like this:

Sum({<MDate={'>$(=Date(median(MDate)+if(odd(count(distinct MDate)),1,0)))'}>} sorg_delta)

The complete expression used in my charts subtracted the sum of earlier date measurements from the sum of later date measurements. Whether that difference is a positive or negative number tells whether the business measurement is getting “better” or “worse” and the size of the difference tells us how much better or worse the measurement has become. That logic was used with a heat map feature to color bars in a graph either reddish or greenish and it helped turn a simple bar graph into a relatively simple-to-understand multivariate analysis chart.
★★★

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!

* * *

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!

* * *

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 )

★ ★ ★

Set analysis and chart dimension

Using set analysis syntax in a chart expression can be useful but remember that set analysis is based on the concept of selection -- it still must work within the dimension of the chart.
For example, if you have a document where HFcst and Lag are two fields, you might have a chart expression that looks like this:
Sum({$<Lag={2}>} HFcst)
That expression is a sum of HFcst with the set analysis syntax working like a selection override specifying that within the Sum function it should use the selection of Lag=2. You couldn't use that expression in a chart where Lag was one of the dimensions and have it work the way you might expect because the expression still must respect the dimensions of the chart -- each row of the chart must match a particular dimension value. But, as long as Lag is not used as the chart dimension then the expression could be very useful (perhaps as a comparison to the current Lag selection).