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

There is a little known function in QlikView that hardly anyone uses and that doesn’t do very much, but still has a tremendous impact on many of the calculations made in QlikView.

 

It is the Only() function.

 

It returns the value of a parameter – but only if there is just one possible value. Hence, if you have a one-to-one relationship between the chart dimension and the parameter, the Only() function returns the only possible value back. But if there are several values, it returns NULL.

 

Entity model.png

 

The Only() function is an aggregation function, which means that it uses many records as input and returns one value only. The Sum() and Count() functions are examples of other aggregation functions. Aggregations are used whenever you have a group of records and need to show only one value representing all records.

 

When you think about it, QlikView uses aggregations in virtually all calculations: The expression in a chart, in a sort expression, in a text box, in an advanced search and in a calculated label are all aggregations and cannot be calculated without involving an aggregation function.

 

But what if the user enters an expression that lacks an explicit aggregation function? What does QlikView do then? For example, if the sort expression is set to “Date”? Or if there is an advanced search for customers using the expression “=Product='Shoe' ” (the intent is to find customers that have bought this product)?

 

This is where the Only() function affects the calculation without the user knowing it; if there is no explicit aggregation function in the expression, QlikView uses the Only() function implicitly. Hence, in the above cases, “Only(Date)” is used as sort expression and “=Only(Product)='Shoe' ” is used as search criterion.

 

Sometimes the new expression returns a result that the user does not expect. Both the above examples will work fine for cases when there is only one possible value of Date or Product, but neither of them will work for cases when there is more than one value.

 

Therefore, when you write expressions you should always ask yourself which aggregation you want to use, or: Which value do you want to use if there are several values? If the answer is that you want to use NULL to represent several values, then you indeed want to use the Only() function and you can leave the expression as it is.

 

But if you do not know the answer, then you should probably think again. For numbers, you probably want to use Sum(), Avg() or Min() instead and for strings you may want to use Only() or MinString(). For debugging you can always use something like, “Concat(distinct <Field>, ',')” and analyze the result.

 

But you should not leave your expression without an aggregation function.

 

HIC

 

Further reading related to this topic:

It’s all Aggregations

Use Aggregation Functions!

21 Comments
IAMDV
Luminary Alumni
Luminary Alumni

Thank you HIC. Interesting to know QlikView assigns ONLY() implicity when we don't use the Aggregation.

Quick Question : According to reference guide...

"Expression must not contain aggregation functions, unless these inner aggregations contain

the total qualifier."

Why do we need to use Total Qualifier in the inner aggregation? Because the aggregation anyway returns a scalar value and ONLY() can show the value right...Is there a specific reason to use Total Qualifier?

Thanks,

DV

www.QlikShare.com

0 Likes
11,376 Views
hic
Former Employee
Former Employee

In the Reference Manual, you can find this sentence in the section "Chart Aggregation Functions" right before the BNF descriptions of the different aggregation functions:

 

sum ( [ distinct ] [ total [<fld {, fld}>] | all [<fld {, fld}>]] expression )

Hence, the word expression refers to the expression written inside the aggregation function. This means that the general rule is that it is not possible to use an aggregation function inside another aggregation function. And this is also how you normally would use an aggregation function.

However, there are two exceptions to this general rule.

  1. You may use an inner aggregation function if it contains the total qualifier, e.g. "Sum(if(value>Avg(total value),value))". In such a case the inner aggregation is similar to a constant (cheap to calculate) and the expression is well defined. Hence QlikView can calculate the expression. Without the total qualifier, the inner aggregation doesn't have a grouping symbol and the expression wouldn't be well defined.
  2. You may use an inner aggregation function if it resides inside the Aggr() function, e.g. "Sum(Aggr(Avg(value),Customer))". In such a case the inner aggregation is calculated first for each grouping value of the Aggr function, and the outer aggregation operates on the Aggr array. This two-step aggregation is more cpu-consuming than a single aggregation.

So the text in the reference manual is correct.

Does this make sense?

HIC

11,376 Views
IAMDV
Luminary Alumni
Luminary Alumni

Totally makes sense. Very clear. Thank you very much.

DV

0 Likes
11,376 Views
Not applicable

Amazing. Qlikview is so vast ...

0 Likes
11,376 Views
sudeepkm
Specialist III
Specialist III

this is superb. thanks a lot.

0 Likes
11,376 Views
Not applicable

I was confused by this:

Sometimes the new expression returns a result that the user does not expect. Both the above examples will work fine for cases when there is only one possible value of Date or Product, but neither of them will work for cases when there is more than one value.


I thought it meant "only one possible value for the entire dataset", but it means, "only one possible value given the Dimension context when that expression is evaluated"


In other words, in this table:

Customer     Date     Product

A                   6/1     Shoes

A                   6/2     Shoes

B                   5/1     Shirt


And:

1) You create List view

2) If the Dimension is "Customer"

3) And the Sort Expression is "Date"; which implicitly translates to Only(Date) as Henric points out

Since there is more than one "Date" value for the given Customer "A";

Then the [fixed typo] Only(Date) function returns Null when the Customer is "A"

And thus Null (for Customer A) is compared to 5/1  (Customer B) to decide how to sort;

Null comes before 5/1

The list looks like this; A before B:

A

B


But in this case:

Customer     Date     Product

A                   6/1     Shoes

B                   5/1     Shirt


The implicit Only(Date) function returns 6/1 when Customer is "A"

And thus 6/1 is compared to 5/1 (Customer B) to decide how to sort;

6/1 comes after 5/1

The list looks like this; A after B:

B

A


NOTE: Fixed Typo which Henric mentions below.

11,376 Views
hic
Former Employee
Former Employee

You are absolutely right that "only one possible value" relates to the context; the Aggregation scope. You are also right that this concerns the sort expression also. In fact, it concerns all expressions (except calculated dimensions) because they are all aggregations.

HIC

PS Small typo in your example: You have written Only(Customer) but I think you mean Only(Date).

0 Likes
9,833 Views
adarsh_a
Contributor III
Contributor III

Nice Article

9,833 Views
md_qlikview
Creator II
Creator II

thank u sir, for sharing this valuable information

0 Likes
9,832 Views
Not applicable

I have used Only() function repeatedly because it allows for adding the set analysis to find a single value. This is useful for labeling when the descriptions can change but an ID value remains the same. Instead of hard-coding an expression label, in some cases I can use something like this:

=Only({<MyField_Id = {1} >} MyField_Description)

as the label. This way changing the descriptions in the source data will be reflected automatically in the charts.

It is also useful in some more complicated expressions, but I think dynamic labeling is a good example of the usefulness of this function.

-Henry

9,832 Views