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
Not applicable

Nice Post Henric.

I have one question about the Aggregations in Pivot table. If we selected the sum of Partial Sums in Pivot table, the expression will be evaluated over the dimensions for Total (like expression total in the Straight table) or simply expression values will be sumed (like sum of rows in the Straight table)

0 Likes
3,058 Views
hic
Former Employee
Former Employee

A pivot table always displays "Sum of Expression" as partial sums and as total. But in a straight table you can choose between "Sum of Expression" and "Sum of Rows". The two calculations will be different only if a specific transaction (and thus number) can belong to two dimensional values at the same time.

HIC

0 Likes
3,058 Views
Not applicable

Henric, thank you for your explanation. My question is "What is the ONLY() function?". I can't find it documented anywhere in the QV manual or reference. I get some idea of how it would be used from your essay, but I would like further information. Thanks.

0 Likes
3,026 Views
hic
Former Employee
Former Employee

It is documented in the manual:

"If expression or field iterated over the chart dimension(s) contain one single value, that value is returned, else NULL is returned. Only can return numeric values as well as text values."

But you can also read about it here: The Only Function

HIC

0 Likes
3,026 Views
Not applicable

Very useful post Henric.

Thanks,

Anosh

0 Likes
3,026 Views
Not applicable

Thanks. Very helpful.

Regards,

KC

0 Likes
3,026 Views
Anonymous
Not applicable

Thanks Henric for your post,

I have a problem with a table and I don't know if we can solve this using aggregation.

I have the column HIT (1 or 0 values) , to get this values  I use this formula

IF(Sum(SIN_QT_UNITS)=0 and Sum(IFO_QT_GROSS_UNITS)=0,0,

IF( (IF( Sum(SIN_QT_UNITS) = Sum(IFO_QT_GROSS_UNITS),0,

          ((Fabs(Sum(SIN_QT_UNITS) - Sum(IFO_QT_GROSS_UNITS)))/Sum(SIN_QT_UNITS)))) <0.23,1,0))

Now I want to build a graph and I have to count the column Hits if this column is 1. It is possible to do this or I have to calculate it on the scripts

Thanks for your help

Best regards!!

Captura.PNG

0 Likes
3,026 Views
ecolomer
Master II
Master II

Thank's for sharing

Good job

0 Likes
3,026 Views