Updated March 22, 2023
Introduction to QlikView Function
Functions are very generic and come in handy when you have to do some specific task repeatedly. In simple words, it is a block of code that performs some task whenever called. QlikView Functions also work in the same way. QlikView provides basic functions like sum, average, etc. as well as advanced functions like functions for loading the data into the editor. It takes required arguments to accomplish the defined actions. These functions perform mathematical operations as well as other analytical operations on the in-memory data. QlikView functions provide the capability to the tool to perform calculations, interpret the data in a repeated manner and much more. Script Editor also uses Qlikview functions for managing operations like loading data, transforming data, aggregating data, etc. using script functions. Charts functions are being used in expressions/measures that we create for QlikView charts.
QlikView Functions List
QlikView offers several functions like
- Color – Functions like ARGB(), HSL(), RGB(), Color() etc. comes under this category that is used to evaluate color properties of chart objects.
- Conditional – Functions like if(), match(), mixmatch() etc. are used for evaluating a condition in load script and chart expressions.
- Aggregation – Functions like group(), sum(), count(), min(), max() etc. are used for mathematical operations on the data. These functions can take any number of arguments and returns a single aggregated output.
- Counter Functions – Functions like RowNo(), autonumber(), fieldvaluecount() etc. are used with reference to record counts.
- Date and Time – There are many date and time functions available with the QlikView. Functions like second(), hour(), month(), year() etc. are used to transform and convert date and time values both in script loader and chart expressions.
- Exponential and Logarithmic – Functions like exp(), pow(), sqrt(), etc. are used for performing exponential and logarithmic calculations.
- Financial – Functions like PV(), FV(), Pmt() etc. are used to calculate payments and interest rates. For e.g. PV() function will calculate the Present value of an investment.
- Formatting – Functions like Date(), Interval() etc. comes under this function category. They are used for changing the format of the dates or numeric fields.
- General Numeric – Functions like fabs(), frac(), sign() etc. are generic functions. E.g. Fabs() returns absolute value of any numeric field.
- Inter Record – Functions like Above(), Below(), Top(), etc. are used when a value from previous records is needed for the execution of the expression for the current record.
- Logical Function – Functions like IsNum(), IsText() are used to evaluate logical operations. Both functions can be used for load script and chart expression.
- Null Function – Null() and IsNul() are used to detect Null values.
- Range Function – Functions like RangeMax(), RangeCount(), RangeAvg() etc. are used to work with ranges like finding maximum value in an array of values.
- Ranking Function – Rank() and HRank() are used to evaluate the rank of a record in a data set.
- String Function – Left(), Right(), Trim() etc. are used for handling and manipulating strings. These functions can be used in both Script loader as well as in chart expressions.
Types Of QlikView Functions
QlikView provides many functions in different categories which solve many different kinds of business problems.
1. General Numeric Functions
These functions perform basic numeric operations on the dataset. Combination and Permutation functions, Modulo functions, Parity Functions, and Rounding Functions come under this category of functions. E.g. fact(x), div(x,y), mod(x,y), fmod(x,y) etc.
2. Range Functions
These functions evaluate different types of ranges from an array-like maximum range or minimum range etc. Basic Range functions like RangeMax(), RangeMin(), RangeMode(), Counter Range Functions like RangeMissingCount(), RangeTextCount(), Statistical Range Functions like RangeAvg(), RangeSkew(), Financial Range Functions like RangeIRR(), RangeNPV() comes under this category. These functions can be used in script load as well as chart expressions.
3. Exponential and Logarithmic Functions
These functions evaluate exponential and logarithmic equations for a dataset. These functions can be used in script loader as well as in chart expressions. Eg. exp(), log(), log10(), pow() etc.
4. Trigonometric and Hyperbolic Functions
These functions perform trigonometric and hyperbolic operations and measure angles in radians. E.g. cos(), sin(), tan() etc. comes under this category.
5. Financial Functions
These functions are used to calculate payments and interest rates on the amount field in a dataset. E.g. FV(), nPer(), Rate() etc.
6. String Functions
These functions are used for handling and manipulating strings in different ways. These functions take string parameters and return string operations. E.g. len(), left(s,n), right(s,n) etc.
7. Mapping Functions
These functions are used for handling mapping tables. E.g. ApplyMap(), MapSubstring() etc.
8. Inter Record Functions
These functions are used when the previous output is needed for the next input. It can be used in both Script Loaded as well as in chart expressions.
9. Conditional Functions
These functions are used to evaluate conditional expressions and return results as per the conditions. E.g. alt(), if(), match(), wildmatch() etc.
10. Logical Functions
These functions are used to evaluate logical operations like finding out whether a data is a numeric or text. E.g. IsNum(), IsText()
Examples
Everyone is aware of basic functions and QlikView has the capability of solving the most complex problems as well. So let’s talk about some of the complex tasks which can be easily achieved by QlikView functions. We all know that Qlikview provides Statistical functions to operate and analyze data. Let’s understand how to use the chi2 test function on our in-memory data. Before proceeding, we have to first load the data into the script editor.
Sample Data-
| I | II | III | IV | V | VI | |
| Team A | 100 | 70 | 80 | 105 | 203 | 91 |
| Team B | 85 | 21 | 118 | 178 | 45 | 15 |
X – Teams, Y – Quantity Sold, Z- Months
The above data is the number of products sold by two teams in 6 months. We want to understand if any significant relationship is there between two teams by observing their selling pattern over 6 months.
Once data is loaded we have to load chi2 test functions to perform the chi2 test on our data. Open Script Editor, select Edit script and add the following command to load chi2 function and pass our data for the execution:
Load X,
Chi2Test_chi2(X, Z, Y) as chi2_value,
Chi2Test_df(X, Z, Y) as deg,
Chi2Test_p(X, Z, Y) as p_value,
Sample_data group by X;
Output:
| X | Chi2_value | Deg | P_value |
| Team A | 107.38 | 5 | 2.2 |
| Team B | 259.09 | 5 | 2.2 |
Conclusion
QlikView provides many built-in functions that run on in-memory data and hence proves to be very efficient. These functions have been organized in many categories to solve varied problems. And it is very easy to visualize the results as you can show the outcome of any function on the Table box. There are many other functions with which you can do a lot with your data. I must suggest to check out these functions to make your work simpler.
Recommended Articles
This is a guide to QlikView Functions. Here we discuss the Types Of QlikView Functions in different categories that solve many different kinds of business problems. You may also have a look at the following articles to learn more-
