Qlik Community

Qlik Design Blog

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

Employee
Employee

It’s all Aggregations

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
Contributor II

hi HIC,

Nice blog..

Thanks,

Kalyan

155 Views
cabhijit
Valued Contributor

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
155 Views
Employee
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

155 Views
cabhijit
Valued Contributor

Got it.. Thanks ...

0 Likes
155 Views
dvqlikview
Honored Contributor II

Nice post HIC. Very useful! Thanks.

0 Likes
155 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
155 Views
Not applicable

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

0 Likes
155 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
155 Views
MVP
MVP

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

0 Likes
155 Views
MVP
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 :-)

155 Views
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
155 Views
Employee
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
155 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
155 Views
Employee
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
155 Views
Not applicable

Very useful post Henric.

Thanks,

Anosh

0 Likes
155 Views
Not applicable

Thanks. Very helpful.

Regards,

KC

0 Likes
155 Views
ce_gutierrez
New Contributor

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
155 Views
ecolomer
Honored Contributor II

Thank's for sharing

Good job

0 Likes
155 Views