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

★★★

Problem: Drag and Drop Straight Table Columns Not Working

One of the other consultants at work came to me last week with a seemingly odd problem. She had a document with a straight table that originally had one dimension column. She added two more dimension columns and QlikView added the new columns on the right-hand side of the table. But, my co-worker wanted all three dimension columns to be on the left of the table which is the way we usually set up a straight table. Normally, dragging the new columns over to the left should be quick and easy… but, no matter how many times she tried, the new columns could not be dragged and stayed stubbornly on the right.

After close examination, some trial and error, and a bit of logical thinking; the source of the problem became clear. The thing that was interfering with the drag-and-drop of the dimension columns was the few columns in the chart that were not visible. In this case, there were two kinds: a hidden column, and three conditional columns that would only be visible under specific conditions.

The source of the problem suggested a solution:  right-click on the straight table, select Properties and choose the Presentation tab. One by one, click on each of the columns shown on the Presentation tab and make sure the Show Column radio button is selected for all columns (not Hidden or Conditional). Then, when all columns are visible, the drag-and-drop will work normally. After rearranging the columns as desired, use the chart Properties and Presentation tab again to restore the hidden columns and conditional columns.
One of the blog comments below suggests there is another type of non-visible column that would affect a Table Box: a column referring to a field no longer available in the document. You would have to edit properties for the Table Box and remove the field that no longer exists to restore drag-and-drop capability.

★★★

Note About Dollar-sign Expansion

Katrina, from the Marketing Group, came to me yesterday with a QlikView document she was working on. It had three scatter plot charts arranged side-by-side and she wanted to make the side-by-side comparison easier by making them all have a similar Y-axis. She knew she wanted something for the static max property of the charts so that each would have the same maximum value for the Y-axis. She knew the basic expression to calculate the maximum value but the charts have a cyclic or cycle-group in the dimension and she needed an expression that would work with the cycle group.

I knew that she needed a dollar sign expansion (to handle the cycle group) and an aggr function (to find the maximum value by dimension) so I scribbled an expression like this on a sheet of note paper and Katrina left to try it out:

Max( aggr( your-calculation-of-y-value, $(=getcurrentfield( your-cycle-group-name )))

An hour later, I got an email from Katrina saying that the expression wasn't working. I decided to take a closer look at her document. The cycle group dimension in the charts is named “Scatter ExErr”. It contains a space or blank in the middle of it—so when it is used in an expression it must be surrounded by square brackets like this [Scatter ExErr]    (Note that double-quotes should work as well as square brackets.)

But, the expression still wasn't working correctly and it returned a null value. When I examined the field names used in the cycle group I saw that some of those also contained a space inside the field name. When you use a dollar sign expansion with a getcurrentfield function in your expression you can think of it as though the dollar-sign expansion becomes the field name before QlikView calculates the expression. Since the field name contains an embedded space then it must also be surrounded by square brackets and in this expression that means surrounding the dollar-sign expansion in square brackets. Katrina’s expression now has two pairs of square brackets and looks something like this:

Max( aggr( your-calculation-of-y-value, [$(=getcurrentfield([Scatter ExErr] )] ))

In documents that I develop, I avoid putting embedded spaces into any names that are not actually visible in the charts and I use an underscore character if it is needed to make a field or object name easier to read. If you decide to name things with names containing embedded spaces remember that you usually have to surround the name with square brackets or double quotes when the name is used in a function or expression.

★★★

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

Inline Load with Where Clause

You wouldn’t ordinarily use a Where clause with an inline load… after all, if you didn’t want all of the rows why bother typing them? But, as an example, I had a project two weeks ago where an inline load with a Where clause helped solve a problem. It was a QlikView document that could load data from any one of several dozen companies. The data included a company code but not the company name or any other friendly details for me to use in the document.

I coded an inline load with a row for each of the possible companies. In the loadscript, after loading the company data, I coded an extract of the company code from the data and stored it in a variable named var_cmp_code.

The inline load, simplified here, looked something like this:

COMPANY_INFO:
Load * inline
[cmp_code, company_name, company_industry
BA, Boeing, Aerospace
CCC, Chesterfield Coal Co, Mining and Minerals
LPL, London Pastry Ltd, Stale Biscuits]
Where cmp_code = '$(var_cmp_code)';

Since each load of the document is loading data for one company, the Where clause on the inline load makes it load one row only and each field name, therefore, has only one value. That allowed me to use company_name or any of the other field names in chart titles and text boxes. For example, I used a text expression in a chart title like this:  ='Future Health Care Risk by Year for ' & company_name

Remember that an inline load is like any other load except that the data is typed directly into the loadscript instead of being loaded from a separate file or database. In addition to a Where clause, you could code a While, Autogenerate, mapping table, Concatenate, Qualify, or the various types of joins available in a loadscript.

★★★

Oracle Hint in the Loadscript

People who load data from an Oracle database into QlikView may sometimes want to use an Oracle hint in the SQL query code. An Oracle hint tells Oracle about how you would like it to plan and execute the query in order to get faster, more efficient execution.

The Oracle hint syntax is actually a comment embedded in the SQL query. For example, the hint to tell Oracle to use up to four parallel processors might look like this in the first line of the query:
SELECT /*+ parallel(4) */ CUSTOMER, SUM(ORD_QTY) FROM   . . .

If you simply code a query like that in the loadscript you won’t get an error or a warning, the query will return data, but you won’t get the performance improvement you were hoping for either. That’s because QlikView doesn’t ordinarily pass comments through to the SQL processor. Oracle would never see your hint.

What is needed, is for you to tell QlikView not to strip out the comments before passing the query into the SQL processor. Code a line like this before your loadscript table definition:

Set StripComments = 0;

ORDER_TABLE:

SELECT /*+ parallel(4) */ CUSTOMER, SUM(ORD_QTY) FROM   . . .

The StripComments variable is a system variable that controls QlikView behavior. Setting the variable to zero tells QlikView not to strip out the comments. It is a good practice to turn StripComments back on after the query with a line like this:
Set StripComments = 1;

One thing to look out for is any other comments in your code. If you have a regular loadscript comment //(a comment beginning with two slashes) mixed in with your SQL code then it will cause a SQL error when it gets passed in to the SQL processor.

★★★