Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
1. Full Composition Usage
It is an example where the complete composition of a Set Analysis Expression is utilized, including the Identifier, Operators, and Modifiers
Syntax:
Aggregation({Identifier <Dimension1 = {"Filtered Value"}, Dimension2 = {"Filtered Value"} >} field)
Example:
Sum({1 <Region={"Chicago"}, Product-={"Lamborghini"}>} Sales}
Qlik will accept three types of Syntax for string representation (“”, ”, []) Double Quotes, Single Quotes, OR Square brackets
Sum({<Region ={ “Chicago” }>} Sales)
Sum({ <Region ={ 'Chicago' }> } Sales)
Sum({ <Region ={ [Chicago] }> } Sales)
Sum of motor car sales in Texas excluding Tesla. Without changing the user’s filter.
2. Composition with multiple values
There will be instances where multiple values against multiple dimensions are advantageous to be set and defined.
The composition of the setting against specific dimensions allows one to use values or properties that you wish to include or exclude.
Syntax:
{Selection < Dimension1 = {value1, value 2, …} [, Dimension2 = {value1, value2, …}] >}
Example:
Sum({$< Region={"Texas","California"}, Product = {"Tesla","Lambo","Aston Martin"} >} Sales)
We return the sum of sales in Texas and California of Tesla, Lamborghini, and Aston Martin vehicles.
Sum($< Region= {}, Product{ "Tesla","Lambo","Aston Martin" } > Sales)
The sum of sales of Tesla, Lamborghini and Aston Martin vehicles where the region field is empty is returned.
3. Search Composition within advanced Qlik Set Analysis
You can place search conditions into the composition of the Set Analysis Expression.
Syntax:
{<Dimension = {" *string or value to be searched* "}>}
Wildcard characters are used * and ?
- * = interpreted against any sequence of characters
- ? = interpreted against any single character
Multiple searches can be performed:
{< Dimension = {"*partialVal*", "fixed text", "?erm*"} >
4. Composition using the dimension of numbers
Qlik evaluates dates and timestamps as numbers; leverage over this functionality can ease date intervals when interpreted as numbers.
Making use of GREATER THAN or LESS THAN in the Set Analysis Expressions returns predefined data as specified.
Syntax:
{< Year = {"<2022”} >}
All records where the year is less than 2022
{< Sales= {"<100000”} >}
All records where Sales totals are less than 100,000
{< Year = {">=2021 <=2022"} >}
All records where the year is between 2021 to 2022
{< Sales= {">=50000 <=100000”} >}
All records where Sales are between 50,000 and 100,000
5. Composition with a numerical function
Using numerical functions like MAX(), RANK(), COUNT() or AVG() within the expression is also possible.
Syntax:
<Year = {"$(=max({1} Year))"}>
Setting to return the last year of data
Placing the identifier {1} into the max function ensures maintaining the last year, when selections are made.
Sum({<MotorVehicle = {"=rank(sum(Price), 4)<= 10"}>} Price)
Top 10 Highest Price vehicles
Sum({<MotorVehicle = {"=rank(sum({<Manufacturer = {“Ford”}>} Price), 4)<= 10"}>} Price)
Top 10 Highest Price vehicles by Manufacturer
Excellent explanation. Would you mind sharing how to display all of the column data while excluding one particular column value? say, by showing the combined sales of every nation excluding China.
All I can think of is P() and E() function... below is the expression i have used but not sure what is the issue.
sum (
{$<country =- p({$<country={'China'}>} country)>}
Sales)