0 ratings0% found this document useful (0 votes) 99 views5 pagesSet Analysis in Qlikview and Its Components PDF
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content,
claim it here.
Available Formats
Download as PDF or read online on Scribd
Set Analysis in QlikView — simplified!
Business Ineligence, Qivew by Suni Ray
One of the best practices | follow while preparing any report / dashboard is to provide a lot of context. This typically makes a
dashboard lot more meaningful and action oriented For example, if you just provide numberof units sold bya product fine in
‘2 month, itis good information, but tis notactonable, Kyou add comparison against same month last year, last month or
‘average of relevant product lines inthis month, you have added context othe number. The business user can take more
meaningful actions out of this report / dashboard.
QlikView has feature called SET ANALYSIS that provides us a way to add this context, Set analysis predefines the SET OF
DATA that our charts / tables use, So, using a Set Expression, we can tell our object (chart / table) to display values
corresponding to various sets of data (e.g. a pre-defined time-period, geographic region, product lines etc,). All of the
‘examples, Imentioned above as part of adding context can be accomplished using Set Analysis in Qlikview.
Most of the QlikView Professionals think that SET ANALYSIS is a complex feature. Through this post, | am trying to change
their conviction towards it
What is SET ANALYSIS 7
Set Analysis can be understood by a simple analogy of how Qlikview works. We make selections on certain variables and
the changes reflect in the entire application. This happens because through our selection, we have created a set of data
which we want to use. In a similar fashion, using Set Analysis feature, we can pre-define the data to be displayed in our
charts.
Some features and characteristics for Set analysis are:
© Itis used to create different selection compared to the current application selections:
© Mustbe used in aggregation function (Sum, Count...)
© Expression always begins and ends with curly brackets {} i}
Dota set: likview Output:
[CompanyName] Year | sale || von 2
i matt s000 soe mE 2012
Curent Selection: Yeor=2012
= Agaregote function ured'Sum
F 2o13_| 1000
S 2o13_| 1000
a
Set Analysis always with
curly brackets {}
© Identifier
* Operator
=sum ({1,$} Sale)
* Modifier J
Wentters | [operator | Pa
(itisalways within angle brackets <>)
be
Identifier Description
0 ‘Represents an empty set, no records
1 ‘Represents the set of all the records in the application
$ :Represents the records of the current selection
$1 ‘Represents the previous selection
‘Represents the set of all records against bookmark ID or the
Bookrmark01 bookmark name Examples:
Expressions Results
=sum ({1} Sale) [Return total sales of the application irrespective of selection, it will not disregard dimensions.
sum ({$} Sale) [Return seles for current selection
=sum ({$1} Sale}|Return the sale of previous selection
In below example, Current year selection is 2012 and previous selection was 2013,
year 2
‘2011 OH 2019
Ena
A 1000 ° 1000 3000 1000
8 1000 ° 1000 3000 1000
c 1000 ° 1000 3000 1000
o 1000 ° 1000 2000 1000
€ 1000 ° 1000 2000 1000
F 0 ° 0 1000 1000
6 ° ° o 1000 1000
current |] [ Emptyser ] [current] Altrecoras Previous
Selection | | (a Records) || Selection | | Selected ——-
© I works on set identifiers
Operator Operator Name Description
+ Union Retums a set of records that belongs to union of sets.
- Exclusion Retums records that belong to the first but not the second
* Intersection Retums records that belong to both of the set identifiers.
Symmetric Retums a set that belongs to either, but not both of the set
I Difference identifiers.
Examples:-
Expressions Results
sum ({1-$} Sale) [Return total sales excluding current selection.
Sum [{$/B00kmark_1} Sale) [Return sales of record, which is not common to current selection and Bookmark 1
sum ({$*Bookmark_1} Sale} [Return sales of record, which is common to current selection and Bookmark _1
In below example, Ihave created a bookmark "BOOKMARK_1" for company selection A, B and C. mon 1000 000 2000 000 000
mo 1000 3000 4000 1000 ‘00
20131000 3000 6000 100 7000
Curent Records of Excluding || Common records Not Common
Selection || Company ABC Cure of Current records of
(Representect Selection and Current Selection
by BOOKMARK 1 and
BOOKMARK_1) (Company 4) BOOKMARK_1.
Modifiers:
Ls
© Modifiers are always in angle brackets <>.
© Itconsists multiple felds and all elds have selection criteria
© Condition of fields within modifiers bypass the current selection criteria,
Expressions
Results
=Sum ({$} Sale)
Retum sales of Company A irrespective of selection
=sum ({1-$} Sale)
Return sales of Company A excluding current selection
=Sum ({$}Sale)
Return sales of Company A and B for Current selection
=um({ $< Year=>| Sale)
Return sales forall three years of current selection
tear
my ae
Cnn
Dollar Sign Expansion:
by
Records for Year 2012 ane
‘Company A of Current
Selecting fwe want to compare current year sale with previous year, previous year sales should reflect values in relation to current
selection of year. For example if current selection of year is 2012, previous year should be 2011 and for current selection of
year 2013, previous year is 2012.
jum ({$<¥ear = {$ (=Max (Year)-1)} >) Sale) *
Above expression always retums sale for previous year. Here $ sign (Font color red) is used to evaluate the value for
previous year. § sign is used to evaluate expression and to use variables in set modifiers. Ifwe have variable that holds last
year value (VLASTYEAR) then expression can be written as
‘=Sum ({$vLASTYEAR)} >} Sale) ~
by
Letus take a scenario, where we want to show current sales of the companies who had sales last year.
Expression should be similar lke:
=sum((8<¥
'$ (=Max (Year) )) ,Company_Name=(Companies who had sales last year}> } Sale)
First we have to identify companies who had sales last year, To fix this problem, we will use function P() that is used to
identify values within a field and function E() that exclude values within a field,
: Finally,
Expressions result
Company, Name-p{Company, Name) [al companies who hed sales across year (201107019) | “® Nave
‘Company_Name=P(/aVeori21>] Company_ Name) fallcompanies who hed sales in year (2032)
\Company_Name= (tear s{-Max(vear-1)>] Company_Narie) [all companies who had sales in previous year
expression
=sum(()Company_Name)>)Sale)
This post was an example where we have brought out methods to use SET ANALYSIS in Qlikview. Have you used this,
feature before? Ifyes, did you find it useful? Do you have more nifty tricks to make Set Analysis more interesting? If not, do
you think this article will enable you to use Set Analysis in your next dashboard?
Do let me know your thoughts on using this feature in QlikView.