In this post, we’ll evaluate 2 evasive techniques within Qlik by which essential company concerns can be attended to: Qlik Set Analysis and Qlik Set Operations.
A typical company goal is to broaden item sales or figure out tactical efficiency. These issues typically take a type like among the following concerns and are asked with an eye towards historical efficiency.
- Which of my present consumers acquired my item?
- Which of my present customers are gaining from my programs?
Qlik supplies a selection of tools to help in the responses to these concerns. We will utilize Qlik Set Analysis to recognize consumers with particular qualities or habits and after that integrate this with Qlik Set Operations to even more comprehend where we may anticipate chances.
Qlik Set Analysis
Our sample information set is a list of fictitious consumers and their orders. We understand their geographical information and their order history. From here we can start to obtain some historic patterns and target habits, geographical or other characteristic information from which to recognize extra sales chances.
Let’s start by recognizing those consumers buying bikes. Utilizing Qlik Set Analysis we can recognize those consumers who have actually acquired bikes in the past. One method to do this is the following:
COUNT( {$ << PRODUCTLINE= {"Bikes"} > >} Unique CUSTOMERNAME)
In the table listed below we see the client’s name, a count of consumers and a count of consumers who have actually acquired bikes.
![Qlik Table](https://www.johndaniel.com/content/wp-content/uploads/2021/05/QlikTableExample1.png)
Negating this, we may then anticipate to discover those consumers NOT buying bikes.
COUNT( {$<< PRODUCTLINE-= {"Bikes"} > >} Unique CUSTOMERNAME)
![Qlik Table Example](https://www.johndaniel.com/content/wp-content/uploads/2021/05/QlikTableExample2.png)
We see the 2 nd and 3 rd procedure columns above are not equally special. Why is this?
What is being recognized in the set are the ORDERS instead of the clients and while this is comparable for the very first case, it is plainly not for its negation in the 2nd case.
A more reliable approach to attain this and maintain the capability to successfully recognize the complimentary set is to utilize the P() and E() operates offered by Qlik for this function.
Rather of:
COUNT( {$ << PRODUCTLINE= {"Bikes"} > >} Unique CUSTOMERNAME)
We utilize:
COUNT( {$<< CUSTOMERNAME =P( {<< PRODUCTLINE= {"Bikes"} > >} )> >} Unique CUSTOMERNAME)
This reads as ‘ Which consumers have actually EVER acquired bikes’ where P() shows Possible.
To attain the complimentary set of those consumers who have NEVER acquired bikes [where E() indicates Excluded] we can do among the following:
COUNT( {$<< CUSTOMERNAME= E( {<< PRODUCTLINE= {"Bikes"} > >} )> >} Unique CUSTOMERNAME)
— OR–
COUNT( {$<< CUSTOMERNAME- =P( {<< PRODUCTLINE= {"Bikes"} > >} )> >} Unique CUSTOMERNAME)
We can now observe that for every single client they either HAVE or have actually NOT acquired bikes. (Note– as composed, the Set Analysis will maintain context of any dimensional choices due to the $ notation). As verification of this truth, we can see that the amount of the 2 groups (49 + 43) amount to the overall (92 ).
![](https://www.johndaniel.com/content/wp-content/uploads/2021/05/QlikTableExample3.png)
Qlik Set Operations
As it stands, this can be beneficial, nevertheless the techniques’ worth is magnified when integrated with other sets by means of Qlik Set Operations.
COUNT( {$
<< CUSTOMERNAME =P( {<< PRODUCTLINE= {"Bikes"} > >} )>>.
*.
<< CUSTOMERNAME =P( {<< PRODUCTLINE= {"Aircrafts"} > >} )>>.
} Unique CUSTOMERNAME)
The Bike set component is increased
with the Planes set component to provide us the crossway of these 2 sets. In this case, we have those consumers who have EVER acquired both Motorcycles AND Planes. We can then rapidly control the sets to address which ever concerns we want to position.
Which consumers have EVER acquired bikes, however NEVER acquired Aircrafts?
COUNT( {$.
<< CUSTOMERNAME =P( {<< PRODUCTLINE= {"Bikes"} > >} )>>.
*.
<< CUSTOMERNAME= E( {<< PRODUCTLINE= {"Aircrafts"} > >} )>>.
} Unique CUSTOMERNAME)
Additionally:
COUNT( {$.
<< CUSTOMERNAME =P( {<< PRODUCTLINE= {"Bikes"} > >} )>>.
-.
<< CUSTOMERNAME =P( {<< PRODUCTLINE= {"Aircrafts"} > >} )>>.
} Unique CUSTOMERNAME)
![Qlik Set Operations Summary](https://www.johndaniel.com/content/wp-content/uploads/2021/05/QlikSetOperationsSummary.png)
Qlik Set Operations Summary
Integrating Qlik Set Analysis and Qlik Set Operations
If, rather of looking for easy characteristic identifiers, we want to comprehend behavioral limits, i.e., Sales above $175k, we can take advantage of search in an advanced Qlik Set Analysis.
AMOUNT( {$<< CUSTOMERNAME =P( {<< CUSTOMERNAME= {"= AMOUNT( SALES)>>= 175000″} > >} )> >} SALES)
This can be additional transformed and combined by means of Qlik Set Analysis Functions P() and E() and Qlik Set Operations (* and -) to recognize a really particular subset of consumers for prospective analysis.
Those consumers ...
AMOUNT( {$.
// never ever having more than 175k in sales (see E() leave out function listed below).
<< CUSTOMERNAME= E( {<< CUSTOMERNAME= {"= AMOUNT( SALES)>>= 175000″} > >} )>>.
*.
// who have actually ever acquired Aircrafts (see P() possible function listed below, * operator above).
<< CUSTOMERNAME =P( {<< PRODUCTLINE= {"Aircrafts"} > >} )>>.
-.
// however are not situated in U.S.A. or Australia (see subtraction operator above).
<< CUSTOMERNAME =P( {<< NATION= {"U.S.A."," Australia"} > >} )>>.
} SALES)
![](https://www.johndaniel.com/content/wp-content/uploads/2021/05/QlikTableExample4.png)
See the ‘Combined’ column listed below for the sales of the defined set of consumers.
We now have the capability to ask and address concerns which can target subsets of consumers based upon any characteristic or habits and which can be quickly and dependably controlled without prolonged or intricate modifying.(*)