Types of Qlik Sense Aggregation Functions

Last updated on Nov 15 2021
Debodeep Mukherjee

Table of Contents

Types of Qlik Sense Aggregation Functions

Have a glance at Qlik Sense Formatting Functions

  1. Basic Qlik Sense Aggregation Functions

The basic aggregation functions are the foremost commonly used aggregation functions.

  • FirstSortedValue

This function sorts values during a field supported another field loaded within the same script.

Syntax:

  1. FirstSortedValue([ distinct ] value, sort-weight [, rank ])

Where, value is that the field or dimension which you would like to sort supported the values provided within the sort-weight parameter.

Sort-weight is that the field whose values are going to be sorted from lowest to highest.

Rank sets the nth value from the list of sorted values which you would like the function to return.

For example, using the sample data given below we’ll apply the function and see how it works.

  1. Temp:
  2. LOAD * inline [
  3. Customer|Product|UnitSales
  4. Arman|AA|10
  5. Arman|AA|18
  6. Arman|BB|9
  7. Arman|CC|2
  8. Chandrika|AA|4
  9. Chandrika|BB|5
  10. Chandrika|DD|25
  11. Darsh|AA|8
  12. Darsh|CC|19
  13. Priya|AA|16
  14. Priya|AA|16
  15. Priya|DD|10
  16. ] (delimiter is ‘|’);

Now, we’ll apply the firstsortvalue function thereon .

FirstSortedValue:
LOAD
Customer,FirstSortedValue(Product,UnitSales)
as CustomerRank
Resident Temp Group By Customer;

This returns a table named FirstSortedValue with a field named CustomerRank and Customer.

Customer CustomerRank
Arman CC
Chandrika AA
Darsh AA
Priya DD

 

Here, the firstsortvalue() function sorts the UnitSales values supported Customers from lowest to highest. the littlest value of UnitSales for every customer is taken and returned as smallest to highest. That is, CC=2 is that the lowest for Arman, then AA=4 is that the second lowest for Chandrika, AA=8 for Darsh and DD=10 is that the lowest for Priya but largest of the four customers.

  • Max

This function returns the very best value amongst the evaluated values of a field. you’ll get a selected nth value from the returned values using the rank parameter.

Syntax:

Max(expr [, rank])

  1. Max(expr [, rank])
  • Min

This function returns rock bottom value amongst the evaluated values of a field. you’ll get a selected nth value from the returned values using the rank parameter.

Recommend Reading – Qlik Sense Field Functions

Syntax:

  1. Max(expr [, rank])
  • Mode

This function returns the foremost commonly occurring value or the worth that happens the very best number of times during a field. This function evaluates and returns both text and numeric values from a field.

Syntax:

  1. Mode(expr)
  • Only

This function returns a worth which is that the only possible results of a specific evaluation. This function uses both text and numeric values. If there’s no such unique value which exists because the only value after the evaluation of knowledge values, then the function returns NULL.

Syntax:

  1. Only(expr)
  • Sum

This function calculates the entire of the values given during a field and returns the calculated sum.

Syntax:

  1. Sum([distinct]expr)
  2. Counter aggregation functions

The counter aggregation functions count the amount of values being evaluated during a field and returns that number.

  • Count

This function returns the entire number of values present during a field during a table.

Syntax:

  1. count([distinct ] expression |* )
  • MissingCount

This function returns the amount of missing values during a field or expression.

Syntax:

  1. MissingCount([ distinct ] expression)
  • NullCount

This function returns the amount or count of all the NULLs present in an expression or field of a table.

Syntax:

  1. NullCount([ distinct ] expression)
  • NumericCount

This function counts only the numeric values present in an expression or field and returns the count.

Let’s revise Qlik Sense Counter Functions

Syntax:

  1. NumericCount([ distinct ] expression)
  • TextCount

This function counts only the text values present in an expression or field and returns the count.

Syntax:

  1. TextCount([ distinct ] expression)
  2. Financial aggregation functions

These are the aggregation functions applied to the financial data values and utilized in the financial operations associated with payments and income .

  • IRR

This function calculates and returns the interior Rate of Interest (IRR) value for a series or number of money flow or the cash debited and credited. The IRR is that the rate of interest which an individual receives upon making investments where payments are made (shown by a negative sign) and received (shown by a positive sign). Such payments must occur at regular intervals like monthly or annually.

Syntax:

  1. IRR(value)

For instance, a field named ‘Payments’ contains some values showing income that IRR must be calculated. Suppose the values are, -1000, 3000, 4200, 6800 then the function IRR(Payments) will return the interest value as 0.1634.

  • XIRR

This function calculates the IRR for an outlined period or schedule of a series of money flow. this suggests that the time periods for cash flows don’t need to be periodic. within the function, you’ll specify the payments or cashflow values from the parameter pmt. Also, the schedule of money flow or payments are often set by date.

Syntax:

  1. XIRR(pmt, date)
  • NPV

This function returns internet Present Value (NPV) for a series of future payments supported a reduction rate applied on the payments values over a period.

Syntax:

  1. NPV(discount_rate, value)

Where, discount_rate is that the rate of discount applied over the whole period on the payment values.

value is that the expression or field which contains the payment values.

  • XNPV

This function returns internet Present Value (NPV) for a series of future payments supported a reduction rate applied on the payments values over fixed schedule of your time or date i.e. the time intervals won’t be periodic.

Syntax:

  1. XNPV(discount_rate,pmt,date)

Where, discount_rate is that the rate of discount applied over the whole period on the payment values.

pmt is that the expression or field which contains the payment values.

date is that the expression gives the dates which specifies the schedule of dates which corresponds to the payments we would like to guage .

  1. Statistical aggregation functions
  • Avg

This function returns the typical of all the aggregated values from the info fields.

Syntax:

  1. avg([distinct] expression)
  • Correl

This function returns the coefficient of correlation for the aggregated set of values which exists as a pair of coordinates represented as x and y-values or value1 and value2 within the expression.

We recommend you to read Qlik Sense Integer Functions

Syntax:

  1. Correl(value1, value2)

Where, value1 and value 2 are the series of paired values that we will calculate the coefficient of correlation by the function.

  • Fractile

This function evaluates a fractile value for the given values in aggregation. you’ll set the fraction between 0 and 1 like the fractile value you would like to calculate for a given set of values.

Syntax:

  1. Fractile(expr, fraction)
  • Kurtosis

This function evaluates and returns the kurtosis value for a given set of values. you’ll use the Distinct parameter to specify that each one the duplicate values are going to be disregarded by the function.

 

Syntax:

  1. Median(expr)
  • Skew

This function returns the skewness of the set of values provided for evaluation. you’ll use the Distinct parameter to specify that each one the duplicate values are going to be disregarded by the function. Also, using the parameter expr, you’ll specify the sector which contains values that you would like to calculate the skewness.

You must read Qlik Sense chart

Syntax:

  1. Skew([distinct]expr)
  • Stdev

This function evaluates and returns the quality deviation for a given set of values. you’ll use the Distinct parameter to specify that each one the duplicate values are going to be disregarded by the function. Also, the parameter expr, you’ll specify the sector which contains values that you would like to calculate the quality deviation.

Syntax:

  1. Stdev([distinct] expr)
  • Sterr

This function evaluates and returns the quality error (stdev/sqrt(n)) value for a given set of values. you’ll use the Distinct parameter to specify that each one the duplicate values are going to be disregarded by the function. Also, the parameter expr, you’ll specify the sector which contains values that you would like to calculate the quality error.

Syntax:

  1. Sterr([distinct] expr)
  • STEYX

This function evaluates and returns the quality error value of the anticipated y-value like each x-value in regression. The values that we absorb as input must be in pairs of x and y-values.

Syntax:

  1. STEYX(y_value, x_value)
  2. String aggregation functions
  • Concat

This function returns a combined string i.e. a string resulting from the concatenation of a couple of individual strings.

Syntax:

 

 

 

Syntax:

  1. Concat([distinct]string[,delimiter [,sort-weight]])

Where, the string is the number of individual strings which you want to join or combine.

delimiter is the sign which you want to use to separate the individual values in the joined string.

sort-weight sets the sort order for concatenation of strings i.e. the string corresponding to the lowest value will be concatenated first and so on till the highest value.

For example,

  1. TeamData:
  2. LOAD * inline [
  3. SalesZone|Team|Amount
  4. East|Gamma|20000
  5. East|Gamma|20000
  6. West|Zeta|19000
  7. East|Alpha|25000
  8. East|Delta|14000
  9. West|Epsilon|17000
  • West|Eta|14000
  • East|Beta|20000
  • West|Theta|23000
  • ] (delimiter is ‘|’);
LOAD SalesGroup,Concat(distinct Team,’-‘) as TeamConcat
Resident TeamData Group By SalesGroup;

This will return a field having records with concatenated strings of East and West sales zones.

SalesZone TeamConcat
East Alpha-Beta-Delta-Gamma
West Epsilon-Eta-Theta-Zeta
  • FirstValue

This function returns the last value from the loading of a table and its fields.

Syntax:

  1. FirstValue(expr)

For example,

  1. TeamData:
  2. LOAD * inline [
  3. SalesZone|Team|Amount
  4. East|Gamma|20000
  5. East|Gamma|20000
  6. West|Zeta|19000
  7. East|Alpha|25000
  8. East|Delta|14000
  9. West|Epsilon|17000
  10. West|Eta|14000
  11. East|Beta|20000
  12. West|Theta|23000
  13. ] (delimiter is ‘|’);

The function FirstValue(Team) will return Gamma for the worth East and Zeta for the worth West because the first loaded value.

  • LastValue

This function returns the worth which was loaded last during the loading of a table and its fields.

Syntax:

  1. LastValue(expr)

For example,

  1. TeamData:
  2. LOAD * inline [
  3. SalesZone|Team|Amount
  4. East|Gamma|20000
  5. East|Gamma|20000
  6. West|Zeta|19000
  7. East|Alpha|25000
  8. East|Delta|14000
  9. West|Epsilon|17000
  10. West|Eta|14000
  11. East|Beta|20000
  12. West|Theta|23000
  13. ] (delimiter is ‘|’);

The function LastValue(Team) will return Beta for the worth East and Theta for the worth West because the first loaded value.

  • MaxString

This function finds and returns the last value loaded during a field of a table.

Have a glance at Qlik Sense zone Functions

Syntax:

  1. MaxString(expr)

For example, within the sample script given below, we’ll get the utmost string or the string loaded last so as .

  1. TeamData:
  2. LOAD * inline [
  3. SalesZone|Team|Date
  4. East|Gamma|01/05/2018
  5. East|Gamma|02/05/2018
  6. West|Zeta|01/06/2018
  7. East|Alpha|01/07/2018
  8. East|Delta|01/08/2018
  9. West|Epsilon|01/09/2018
  10. West|Eta|01/10/2018
  11. East|Beta|01/11/2018
  12. West|Theta|01/12/2018
  13. ] (delimiter is ‘|’);

The function, MaxString(Date) will return 01/11/2018 for East SalesZone and 01/12/2018 for the SalesZone West.

  • MinString

This function finds and returns the primary value loaded during a field of a table because the minimum string.

Syntax:

  1. MinString(expr)

For example, within the sample script given below, we’ll get the minimum string or the string loaded last so as .

  1. TeamData:
  2. LOAD * inline [
  3. SalesZone|Team|Date
  4. East|Gamma|01/05/2018
  5. East|Gamma|02/05/2018
  6. West|Zeta|01/06/2018
  7. East|Alpha|01/07/2018
  8. East|Delta|01/08/2018
  9. West|Epsilon|01/09/2018
  10. West|Eta|01/10/2018
  11. East|Beta|01/11/2018
  12. West|Theta|01/12/2018
  13. ] (delimiter is ‘|’);

The function, MinString(Date) will return 01/05/2018 for East SalesZone and 01/06/2018 for the SalesZone West.

  1. Synthetic dimension functions

The synthetic dimension functions create values synthetically which aren’t a neighborhood of the fields that load within the script. The values created by synthetic dimension functions reside during a synthetically created dimension. we will use the values of the synthetic dimension in charts as a calculated dimension and contain values arising from the prevailing dimension from the tables loaded within the script. We call such dimensions as dynamic synthetic functions. The values in such dimensions doesn’t affect by selections made in other fields.

We can use it only in chart expressions and not in script expressions.

  • ValueList

This function creates a group of row labels or strings because the newly formed synthetic dimension which can contain values of calculations made with values of other fields.

Learn more about Qlik Sense Null Functions

Syntax:

  1. ValueList(v1 {,…})

Where v1 shows the list of dimension names that separate by comma which you would like to make .

,…. is that the added list of more dimensions.

For example,

  1. SalesRecord:
  2. LOAD * INLINE [
  3. SaleID|Amount|Year
  4. 1|1|2018
  5. 2|1|2018
  6. 3|1|2018
  7. 4|2|2018
  8. 5|2|2018
  9. 6|2|2018
  10. 7|2|2018
  11. 8|1|2017
  12. 9|1|2017
  13. 10|2|2017
  14. 11|2|2017
  15. 12|2|2017
  16. ] (delimiter is ‘|’);

We will create 3 new synthetic dimensions from the function ValueList()which will use the values given within the table.

IF(ValueList(‘Number of Orders’, ‘Average Order Size’, ‘Total Amount’) = ‘Number of Orders’, count(SaleID),’Average Order Size’, avg(Amount), ‘Total Amount’, sum(Amount) ))

This will give us three new row labels within the resultant table.

Synthetic Dimensions Year Values
Number of Orders 2017 5.00
Number of Orders 2018 7.00
Average Order Size 2017 13.20
Average Order Size 2018 15.43
Total Amount 2017 66.00
Total Amount 2018 108.00

ValueLoop

This function returns a group of values created automatically from iterations occurring from the beginning to finish value. These newly created values reside during a synthetic dimension.

Syntax:

  1. ValueLoop(from [, to [, step ]])

Where, from is that the start value of the range or set of values this function will generate.

to is that the end or last value of the range or set of values this function will generate.

step is that the size of increment for calculating each new value within the range or set of values.

For example, ValueLoop(1,50) will create an inventory of values from 1 to 50.

Or, ValueLoop(2,10,2) will increment each value ranging from 2 to 2 values further and return 2,4,6,8, and 10.

  1. Nested aggregation

Nested aggregation is completed when the user wants to use an aggregation on the results of another aggregation function, hence, referred to as nesting aggregations. In Qlik Sense, you’ll nest up to 100 aggregation functions one within the other. a really important condition for nesting the aggregation functions, you want to use the entire qualifier within the inner expressions whenever you nest a function.

Recommended Reading – Qlik Sense Day Numbering Functions

For example, notice the expression given below,

  1. Sum (If(Year(OrderDate)=Max(TOTAL Year(OrderDate)), Sales))

Here, the Max() function nest in another aggregation function i.e. Sum(). We use the entire qualifier within the inner expression to validate the nesting in Qlik Sense, otherwise, we’ll not accept it.

So, this brings us to the end of blog. This Tecklearn ‘Types of Qlik Sense Aggregation Functions’ blog helps you with commonly asked questions if you are looking out for a job in Qlik Sense BI. If you wish to learn Qlik Sense and build a career in Business Intelligence domain, then check out our interactive, Qlik Sense Certification Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/qlik-sense-certification-training/

Qlik Sense Certification Training

About the Course

Qlik Sense is a revolutionary Business Analytics tool to come from the Qlik stables. It provides powerful self-service analytics that are readily deployable through interactive and personalized dashboards, data visualization techniques and insightful reports. By the end of this Qlik Sense online training, you will be able to perform key skills of the self-service BI tool – Qlik Sense, such as self-service analytics, write data load scripts, data discovery, create dashboards, develop and share apps, create reports, and design and build data visualizations. All these skills will enable you to clear the Qlik Sense certification exam.

Why should you take Qlik Sense Training?

  • The average annual pay for a Qlik Sense Professional is $101,871. -Indeed.com.
  • HSBC, Alstom, Chrysler, Citibank, Accenture and many other MNC’s worldwide use Qlik Sense BI and it has a market share of around 5% globally.
  • By the end of 2020, the market is expected to touch USD 22.8 billion as modern BI and analytics continue to expand more rapidly, Gartner said in a report.

What you will Learn in this Course?

Introduction and Installation of Qlik Sense

  • Need for self-service Business Intelligence/Business Analytics
  • Installation of Qlik Sense and Qlik Sense Desktop

Qlik Sense Features

  • Qlik Data indexing engine
  • Data dimensions relationships
  • Types of Data Loading
  • Types of Concatenation

Data Modelling

  • Qlik Sense data architecture
  • Understanding QVD layer
  • Converting QlikView files to Qlik Sense files
  • Incremental Load
  • Scripting
  • Create Master Calendar

Advance Data Modelling

  • Qualify and unqualify
  • Joins
  • Keep
  • Cross Table
  • Let Vs Set
  • Calendar Table Creation

Qlik Sense Enterprise

  • Various Functions
  • Create QVD Files
  • Read Data for QVD Files
  • Create QVD’s
  • Create Tier 2 Qlik Sense App

Data Visualization

  • Expressions
  • Variables
  • Extensions
  • Data Visualization

Set Analysis

  • Set analysis in Qlik Sense
  • Use set expression like identifiers, operators, modifiers and comparative analysis

Advance Set Analysis

  • Deploy comparison sets and perform point-in-time analysis

Qlik Sense Storytelling

  • Storytelling feature of Qlik Sense
  • Create a story and playback the story

Qlik Sense Visualization

  • Qlik Sense Charts
  • Advanced Charts
  • Creating Dashboards
  • Real Life Examples

Security

  • Security aspects of Qlik Sense
  • Security rules

Got a question for us? Please mention it in the comments section and we will get back to you.

 

 

 

0 responses on "Types of Qlik Sense Aggregation Functions"

Leave a Message

Your email address will not be published. Required fields are marked *