QlikView - Set Analysis
QlikView's Set Analysis feature is used to segregate the data in different sheet objects into many sets and keeps the values unchanged in some of them. In simpler terms, it creates an option to not associate some sheet objects with others while the default behavior is all sheet objects get associated with each other. This helps in filtering the data in one sheet object and seeing the corresponding result in others, while the sheet object chosen as a different set displays values as per its own filters.
Let us consider the following input data, which represents the sales figure of different product lines and product categories.
Product_Line,Product_category,Month,Value Arts & Entertainment,Hobbies & Creative Arts,Jan,5201 Arts & Entertainment,Paintings,Feb,8451 Arts & Entertainment,Musical Instruments,Jan,1245 Baby & Toddler,Diapering,Mar,1247 Baby & Toddler,Toys,Dec,257 Apparel & Accessories,Clothing,Feb,574 Apparel & Accessories,Costumes & Accessories,Apr,1204 Arts & Entertainment,Musical Instruments,Apr,3625 Baby & Toddler,Diapering,Apr,1281 Apparel & Accessories,Clothing,Jul,2594 Arts & Entertainment,Paintings,Sep,6531 Baby & Toddler,Toys,May,7421 Apparel & Accessories,Clothing,Aug,2541 Arts & Entertainment,Paintings,Oct,2658 Arts & Entertainment,Musical Instruments,Mar,1185 Baby & Toddler,Diapering,Jun,1209
The above data is loaded to QlikView memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the "Table Files" option from the "Data from Files" tab and browse for the file containing the above data. A screen appears as shown below.
Create Table Box
Choose all the fields available to create a table box using the menu option Layout → New Sheet Objects → Table Box and a list box containing the month’s field using the menu option Layout → New Sheet Objects → List Box. Also, create a straight table chart showing the total sales under each product category.
Now we can observe the association between these three sheet objects by selecting some values in one of them. Let us select the month Apr and Jan from the Month list Box. We can see the change in values in the Table Box and chart showing the related values.
Next, we clone the sales sum chart to produce a new set of data not associated with other sheet objects. Right click on the chart Sales Sum and click on the option Clone as shown below. Another copy of the same chart appears in the QlikView document.
Next, we choose the second copy of the chart Sales Sum and right click it to get the chart properties. We create an expression called Sales values writing the formula under the Definition tab as shown below.
Applying Set Analysis
On completing the above given steps, we find that when we select the month June we get the associated values in the Table Box and Sales Sum chart. However, the April sales does not change as it is based on the data from the set expression.