Qlik Community

Qlik Design Blog

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

The Only Function

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
Luminary
Luminary

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
2,740 Views

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

2,740 Views
Luminary
Luminary

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

DV

0 Likes
2,740 Views
Not applicable

Amazing. Qlikview is so vast ...

0 Likes
2,740 Views
sudeepkm
Valued Contributor III

this is superb. thanks a lot.

0 Likes
2,740 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.

2,740 Views

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
2,740 Views
adarsh_a
New Contributor III

Nice Article

2,740 Views
md_qlikview
Contributor II

thank u sir, for sharing this valuable information

0 Likes
2,740 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

2,740 Views
kirankkk
Contributor II

Dear Henry,

Thanks for sharing you experience.

I have used only function to restrict one dimension from add-hoc list of dimensions in pivot table.



-Kiran K

0 Likes
2,740 Views
geetaalhan
Contributor

Thanks for the valuable information.

0 Likes
2,740 Views
sibideepak
Contributor II

Helpful !!

0 Likes
2,740 Views
rajkumarb
Contributor II

Thank you! very helpful.

0 Likes
2,740 Views
Not applicable

Worth going through...

0 Likes
2,740 Views
Not applicable

Awesome post! Very insightful!

Thank you

0 Likes
2,740 Views
HirisH_V7
Honored Contributor

Hi ,

Did you solved this!

Picking Customer using maximum date.

Thanks,

Hirish

0 Likes
2,740 Views
Not applicable

Thank you for great explanation. But can anyone post a simple practical example of Only function.

I cannot find it on Qlikview help content online and also on Qlikview community. I just want a basic level example of this function. I am new to Qlikview by the way and still learning a lot about it.

0 Likes
2,740 Views

Let's say that you have a table showing sales per customer. Then you have Customer as dimension and Sum(Sales) as measure.

If you now want to add a measure showing the Country of the customer, you can do this using Only(Country). Using the Only() function instead of a naked field reference "Country" has the advantage that you can use set analysis also.

HIC

2,740 Views
Not applicable

Thank You, Sir.

I would understand it better with some dummy tables and expression.

0 Likes
2,740 Views
sujan24s
New Contributor III

Nice Awesome Post

Thanks

0 Likes
2,740 Views