$(eAmount)
where eAmount is defined like, for instance
LET eAmount = 'sum(Amount)';
If you don't use reusable expressions in QlikView you definitely should start doing it because of 2 reasons:
- Improved maintainability -- you don't have to edit all occurrences of an expression in an application, but only once
- Better readability -- you have much better readability of your expressions because their (sometimes complex) logic is actually replaced with its brief explanation.
While this a generally good practice there are a couple of tricks to get even more value from it.
One trick is to use expansions inside set analysis definitions. In this case we can achieve some sort of flexibility by combining search conditions. For example:
Definition
LET sPrevYear = 'Year={"=only(Year)-1"}';
LET sHeadoffice = 'CostCenter={"Head Office"}';
Examples of use
sum( {< $(sPrevYear)>} Amount)
sum( {< $(sPrevYear), $(sHeadoffice) >} Amount)count( {< $(sHeadoffice) >} Employee)
However we can go even beyond that, and use expansions with parameters inside set definitions. This allows us to achieve 2 goals:
- Reduce number of reusable expressions
- Cover cases with expressions which occur only once and/or has set definition that only slightly differs from an already defined reusable expression
LET ePrevAmount = sum ( {<Year={"=only(Year)-1"}, $1 >} Amount)
Example of use
$(ePrevAmount( Type={Income} )) - $(ePrevAmount( Type={Expense} )) + $(ePrevAmount( Account={91000} ))
The trick is that you can still use such expansion without parameters, so
$(ePrevAmount)
works just fine, because when expanded it turns into
sum ( {<Year={"=only(Year)-1"}, $1 >} Amount)
where $1 appears for QlikView like an absolutely valid naming, which just means nothing specific in this case, so QlikView simply ignores it without raising an error. So, we're getting very flexible reusable expressions.
This approach has one downside that is that QlikView doesn't allow passing strings with commas as expansion parameters because everything after comma is considered as next parameter. Therefore something like
$(ePrevAmount( City={NY}, CostCenter={Headoffice}))
or
$(ePrevAmount( City={NY, Chicago, Boston}))
simply won't work. Adding more parameters for expansion won't help a lot as QlikView's scripting language doesn't support function overloading.
Fortunately, there is a bit dirty but still efficient workaround for this limitation. It is based on the fact that conditions in a set definition actually have boolean operation AND applied between them. For instance set definition
{<Year={2012}, City={NY}, Product={Loan}>}
actually means
Year=2012 AND City=NY AND Product=Loan
However, we can achieve the same logic by using intersection of sets. Therefore we can rewrite the example above as follows:
{<Year={2012}> * <City={NY}> * <Product={Loan}> }
In this case we still can define sets with several conditions, but avoid commas. Therefore, we can use this syntax in our reusable expressions -- we simply replace commas with >*<, which doesn't look very good, but works fine.
Example of use
$(ePrevAmount( Type={Income} >*< Aggregated={Yes} >*< CostCenter={NY} ))
We also can use the same technique to avoid commas in search lists. But in this case we need to model boolean operator OR, so we need to use union of sets instead of intersection:
$(ePrevAmount( City={NY} >+< City={Boston} >+< City={Chicago} ))
Again, this is not very aesthetically pleasing, but in some case this is still better than complex poorly readable and non-reusable expressions.
PS. It should be noted that combination of intersection and union should be done carefully with respect to operation priority. Otherwise it might lead to unexpected results.
UPDATE
Anatoly (sparur) has offered more simple and elegant solution for avoiding commas in his blog (see link in comment below). For those who can't read russian, he suggests to use any other symbol for dividing values (e.g. semicolon) and then replacing it with comma dynamically right before using in expression. For example, instead of
LET ePrevAmount = sum ( {<year={"=only(year)-1"}, $1="">} Amount)
one can write
LET ePrevAmount = sum ( {<Year={"=only(Year)-1"}, $(=Replace('$1', ';', ',')) >} Amount)
and then use expressions like
$(ePrevAmount( Type={Income}; Aggregated={Yes}; CostCenter={NY} ))
or
$(ePrevAmount( City={'NY';'Boston';'Chicago'} ))
Much better, isn't?
