Hi Khalid
Simple example of Aggr.........
I use this primarily when I am dealing with values that require Currency conversion, let's assume that I have a series of rows of data with Transactional values in a transaction currency, and I want to display those values all in US Dollars. If I create a table with country and transaction currency as dimensions then QlikView will understand how to convert each row because the transaction currency is included in the dimension.
Now, my actual requirement is to show only the country as a dimension, I don't want to show the transaction currency, so now when Qlikview displays the chart it really doesn't understand what each transaction currency might be so an expression such as:
=Sum(Sales) * FXRate
will not work, because FXRate in this case would be a random selection of the available FX Rates, in this case I need to use the AGGR() function, quite simply, the AGGR() function works outside of the current dimensions of the chart so you have the ability to break down a single row into it's constituent parts. In this case an expression such as:
=Sum ( Aggr ( Sum ( Sales ) * FXRate , Country , TransCCY ) )
will work....
Notice that I include the dimension of the chart also, this is important because if you miss this out then bear in mind that the Aggr() function works outside of chart dimensions, and therefore the results will not be what you require in this case.
The AGGR() function I suppose is an internal loop mechanism that performs calculations at the granulairty you specify in the expression.
ONLY()
I don't use this much, but here's a description from somebody else on the forum:
Well, only() returns the value if there is one and only one matching value for the given dimensions, else it returns null. Sometimes used when you want to be certain that you won't get anything returned where there are multiple matches. Conveniently, it is also an aggregation expression, allowing you to use set analysis in some cases where you otherwise wouldn't be able to.
