Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
Henric_Cronström

On the discussion forum, I often see people posting questions around expressions that don’t work. When looking at the descriptions, I usually find that the reason is that the expressions lack aggregation functions. So, here is a suggestion...

Always use an aggregation function in your expression.

The reason is that a field reference in an expression always means an array of values. Which in turn means that you must enclose it in an aggregation function to make it collapse into one value:

      OrderDate             An array of values

      Max(OrderDate)     A single value

If you don't use an aggregation function, QlikView will use the Only() function. Hence, if the field reference returns several values, QlikView will interpret it as NULL, and the expression will not be evaluated the way you want it to.

Example 1: Use of the If() function:

If() functions are often used for conditional aggregations:

      If( OrderDate >= vReferenceDate, Sum(Amount) )

At first glance, this expression may look correct: For dates after a reference date, the field Amount should be summed. Right?

Wrong.

OrderDate is a naked field reference: It does not have an aggregation function. Hence, it is an array, possibly with several values, and if so, evaluates to NULL. If you are lucky, there is only one date per dimensional value in your chart, and the expression will calculate fine. However, QlikView will probably not be able to calculate the expression for the subtotals in the chart, since there for those exists several dates.

A correct expression that always works should use a Min() or some other aggregation function in the first parameter of the If() function:

      If( Min(OrderDate) >= vReferenceDate, Sum(Amount) )

Or, alternatively, the If() function should be put inside the Sum() function:

      Sum( If(OrderDate >= vReferenceDate, Amount) )

In the first of the two expressions, the If() function will be evaluated once per dimensional value; in the second once per row in the raw data. The results are slightly different, but both return an answer, as opposed to the original expression. The picture below shows the difference between the expressions, using 2013-02-01 as reference date.

If function.png

Example 2: Sort by expression:

The expression used to sort the dimensional values in a chart is also an aggregation. Often you don’t think about this since you choose an expression that returns just one value per dimensional value, and then a naked field reference works fine.

But sometimes this still doesn't work…

For example, say that you want to show support cases in a CRM system. You create a chart with the support case as dimension and some measure as expression. Of course you want to sort the support cases chronologically, so you use "Sort by Expression" and as expression you choose

      [Opening Date]

This will work in most cases. However, some CRM systems allow you to re-open a support case, hence assigning two opening dates to one single support case.  For these cases, the above expression will not work.

Instead, you should always ask yourself which function to use, should there be two values. The answer is usually Sum(), Avg(), Min() or Max(). In the above case, you should use

      Min([Opening Date]) , or

      Max([Opening Date])

depending on whether you want to use the first or last date.

Bottom line: Use aggregation functions, not just in your chart measures, but also in sort expressions, labels, show conditions, calculation conditions, text boxes, sheet names and searches.

HIC

 

Further reading related to this topic:

It’s all Aggregations

Aggregations and Function Classes

42 Comments
Not applicable

Thank you very much! Made some things clearer to me!

0 Likes
8,896 Views
satyadev_j
Specialist
Specialist

Well articulated. Thanks for sharing!

0 Likes
8,896 Views
eruditio
Partner
Partner

School of Qlik thanks!!

0 Likes
8,896 Views
datanibbler
Champion
Champion

Hi Henric,

that is a very valuable post indeed. I haven't read it in full yet, but I will copy it to Word, make into an epub and read it on the train - for plain reading, I prefer my eReader which means epub is the best format available. I cannot count the times we have already come across a display not showing something just because there were several values in that field acc. to the dimensions we had and thus QlikView would not show it.

I have made quite good experiences (in straight-table charts I use to show details, that is) with using a line_id (rowno()) as additional dimension and then hiding it (on the "presentation" tab). That way, there is only one line per line (logical, no?) and no details are lost.

Best regards,

DataNibbler

0 Likes
8,896 Views
Not applicable

Henric,

I hear the sound of a thousand palms slapping into a thousand foreheads, as people read the first section, and the realisation dawns.  I quickly checked a couple of my documents, where I had some "issues" and there it was the lesser spotted NFR.

Thanks

0 Likes
8,896 Views
fdelacal
Specialist
Specialist

Thanks for share!!

0 Likes
8,896 Views
Marcio_Campestrini
Specialist
Specialist

HIC

Thanks for clarifying that. There really are some points that are not usually taken into account in developing applications

0 Likes
6,851 Views
giakoum
Partner
Partner

Good article indeed!

But should we not point out to people, especially beginners, that expressions like

If( Min(OrderDate) >= vReferenceDate, Sum(Amount) )

Sum( If(OrderDate >= vReferenceDate, Amount) )

are not a good idea to use, and set analysis should be used instead?

Thank you, Ioannis.

6,851 Views
Not applicable

Ioanis

I think beginners would understand

If( Min(OrderDate) >= vReferenceDate, Sum(Amount) )

Rather than:-

Sum ({$<OrderDate = {'>=$(vReferenceDate)}'} Amount))

(I suspect I also got that Set analysis wrong, as I did it from memory)

I would suspect beginners stay away from Set Analysis until they are past the beginners stage, the syntax is far too  obtuse!

Just my opinion though

Richard

6,851 Views
giakoum
Partner
Partner

@Richard :

Yes Set Analysis is tricky, I am having a hard time with it myself. But you have to do it the right way, tricky or not. I have seen many developers, not necessarily beginners, use anything else but set analysis, and I really don't like the results. Very badly coded apps performing terribly... You cannot act as a professional QV developer without it I think.

0 Likes
6,851 Views