Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

I often see incorrect expressions being used in the QlikCommunity forum. Expressions that seem to work correctly – but really don’t…

 

So, let me make this clear: Calculations in QlikView are aggregations.

 

It doesn’t matter if it is a measure in a chart, or a calculated object label, or a show condition for an object, or a calculated color, or an expression search – all expressions in the user interface are evaluated as aggregations. (Except calculated dimensions, and some search strings.)

 

This means that it is correct to use the Sum() function in an expression, since this is an aggregation function - a function that uses several records as input. But if you omit the aggregation function or use a scalar function only, e.g. RangeSum(), you can get an unexpected behavior.

 

Aggr1.png

 

Basically, all field references should be wrapped in an aggregation function. The Aggr() function and some constructions using the total qualifier can even have several layers of aggregations.

 

But if the created expression does not contain an aggregation function, the expression is ill-formed and potentially incorrect.

 

Examples:

     =Sum(Amount)
     =Count(OrderID)
These are both correct aggregations. Amount is wrapped in the Sum() function which will sum several records of the field Amount. OrderID is wrapped in the Count() function, which will count the records where OrderID has a value.

 

     =Only(OrderID)
This is also a correct aggregation. OrderID is wrapped in the Only() function, which will return the OrderID if there is only one value, otherwise NULL.

 

     =OrderID
A single field reference is not an aggregation, so this is an ill-formed expression. But QlikView will not throw an error. Instead it will use the Only() function to interpret the field reference. I.e., if there is only one value, this value will be used. But if there are several possible values, NULL will be used. So, it depends on the circumstances whether an expression without aggregation function is correct or not.

 

     =If(Year=Year(Today()), Sum(Amount1), Sum(Amount2))
Here, both the amounts are correctly wrapped in the Sum() function. But the first parameter of the if() function, the condition, is not. Hence, this is an ill-formed expression. If it is used in a place where there are several possible Years, the field reference will evaluate to NULL and the condition will be evaluated as FALSE, which is not what you want. Instead, you probably want to wrap the Year in the Min() or Max() function.

 

     =ProductGroup= 'Shoes'
     =IsNull(ProductGroup)
These expressions can both be used as show conditions or as advanced searches. However, since there are no aggregation functions, the expressions are ill-formed. If you want to test whether there exists Shoes or NULL values among the field values, you probably want to use the following instead:
     =Count(If(ProductGroup= 'Shoes', ProductGroup))>0
     =NullCount(ProductGroup)>0

 

Conclusions:

 

  • An aggregation function is a function that returns a single value describing some property of several records in the data.
  • All UI expressions, except calculated dimensions, are evaluated as aggregations.
  • All field references in expressions must be wrapped in an aggregation function. If they aren’t, QlikView will use the Only() function.

 

HIC

 

Further reading related to this topic:

The Only Function

Use Aggregation Functions!

18 Comments
kalyandg
Partner - Creator III
Partner - Creator III

hi HIC,

Nice blog..

Thanks,

Kalyan

6,395 Views
AbhijitBansode
Specialist
Specialist

Very useful information. Thanks Henric.

Though, I have one question.

Even if my shown conditions and advanced search expressions are ill formed, they do the desired job.

Can you help me find out any sceanrio where there is consequence of using ill formed expressions ?

Thanks,

Abhijit

0 Likes
6,395 Views
hic
Former Employee
Former Employee

Whenever there is more than one possible value.

Example:

If you use If(Year=Year(Today()), Sum(Amount1), Sum(Amount2))

as expression in a chart where you have Year as dimension, you will never have a problem since the experssion is evaluated in a scope where there always is only one Year. But if you use the same expression with another field as dimension, e.g. Product, you will most likely get problems since a specific product can be associated with several years.

HIC

6,395 Views
AbhijitBansode
Specialist
Specialist

Got it.. Thanks ...

0 Likes
6,395 Views
IAMDV
Luminary Alumni
Luminary Alumni

Nice post HIC. Very useful! Thanks.

0 Likes
6,395 Views
Not applicable

Hi Everyone 

I am trying o display the Top N Items by Sales in a straight table.
I intend to have an (N+1)th entry where I want the sum of the remaining Items

How can I implement this ?
Kindly help

0 Likes
6,395 Views
Not applicable

try 'dimension limits' feature in the properties of Straight Table

0 Likes
5,104 Views
Not applicable

I don think Dimension Limit is going to work as

1) We can not find the sum of sales of remaining items without tweaking the expression which currently looks like sum(sales)

2) The dimension limit feature has more standard options like top n but the trouble is to get the sum of the remaining items after the top 10/Top N items.

0 Likes
5,104 Views
swuehl
MVP
MVP

Have you looked into the 'Others' option on Dimension Limit tab?

0 Likes
5,104 Views
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Nice post, HIC! I always knew intuitively that expressions with field names without an aggregation function are bad, but I could never put it into words so eloquently 🙂

5,104 Views