QlikView has some great tools to allow users to generate their own content. However, sometimes users don't want to learn those skills and would like to quickly just to be able to analyze data.
In this recipe, we will show how to create an easy-to-use dynamic analysis tool using some of the features from QlikView 11.
Load the following script:
// Set the Hide Prefix
Set HidePrefix='%';
// Load the list of dimensions
DimensionList:
Load * Inline [
%Dimensions
SalesPerson
Country
City
Product
];
// Load the list of expressions
ExpressionList:
Load * Inline [
%ExpressionName
Total Sales
Order Count
Avg. Sales
];
// Load the Sales data
Data:
LOAD * INLINE [
SalesPerson, Country, City, Product, Sales, Orders
Joe, Germany, Berlin, Bricks, 129765, 399
Joe, Germany, Berlin, Brogues, 303196, 5842
Joe, Germany, Berlin, Widgets, 64358, 1603
Joe, Germany, Berlin, Woggles, 120587, 670
Joe, Germany, Frankfurt, Bricks, 264009, 2327
Joe, Germany, Frankfurt, Brogues, 369565, 3191
Joe, Germany, Frankfurt, Widgets, 387441, 5331
Joe, Germany, Frankfurt, Woggles, 392757, 735
Joe, Germany, Munich, Bricks, 153952, 1937
Joe, Germany, Munich, Brogues, 319644, 645
Joe, Germany, Munich, Widgets, 47616, 2820
Joe, Germany, Munich, Woggles, 105483, 3205
Brian, Japan, Osaka, Bricks, 17086, 281
Brian, Japan, Osaka, Brogues, 339902, 2872
Brian, Japan, Osaka, Widgets, 148935, 1864
Brian, Japan, Osaka, Woggles, 142033, 2085
Brian, Japan, Tokyo, Bricks, 161972, 1707
Brian, Japan, Tokyo, Brogues, 387405, 2992
Brian, Japan, Tokyo, Widgets, 270573, 3212
Brian, Japan, Tokyo, Woggles, 134713, 5522
Brian, Japan, Yokohama, Bricks, 147943, 4595
Brian, Japan, Yokohama, Brogues, 405429, 6844
Brian, Japan, Yokohama, Widgets, 266462, 3158
Brian, Japan, Yokohama, Woggles, 477315, 5802
Joe, UK, Birmingham, Bricks, 23150, 1754
Joe, UK, Birmingham, Brogues, 200568, 1763
Joe, UK, Birmingham, Widgets, 262824, 617
Joe, UK, Birmingham, Woggles, 173118, 5359
Joe, UK, London, Bricks, 621409, 712
Joe, UK, London, Brogues, 504268, 2873
Joe, UK, London, Widgets, 260335, 1313
Joe, UK, London, Woggles, 344435, 743
Joe, UK, Manchester, Bricks, 401928, 1661
Joe, UK, Manchester, Brogues, 7366, 2530
Joe, UK, Manchester, Widgets, 6108, 5106
Joe, UK, Manchester, Woggles, 269611, 4344
Mary, USA, Boston, Bricks, 442658, 3374
Mary, USA, Boston, Brogues, 147127, 3129
Mary, USA, Boston, Widgets, 213802, 1604
Mary, USA, Boston, Woggles, 395072, 1157
Michael, USA, Dallas, Bricks, 499805, 3378
Michael, USA, Dallas, Brogues, 354623, 18
Michael, USA, Dallas, Widgets, 422612, 2130
Michael, USA, Dallas, Woggles, 217603, 2612
Mary, USA, New York, Bricks, 313600, 6468
Mary, USA, New York, Brogues, 559745, 1743
Mary, USA, New York, Widgets, 94558, 2910
Mary, USA, New York, Woggles, 482012, 3173
Michael, USA, San Diego, Bricks, 95594, 4214
Michael, USA, San Diego, Brogues, 24639, 3337
Michael, USA, San Diego, Widgets, 107683, 5257
Michael, USA, San Diego, Woggles, 221065, 5058
];These steps show you how to create dynamic ad hoc analysis in QlikView:
Open the Select Fields tab of the sheet properties. Select on the Show System Fields option (so you can see the hidden fields). Add a list box on the display for the %Dimensions and %ExpressionName fields.
Create a new bar chart.
Set the title of the chart to
Sales Analysis. Turn on Fast Type Change for Bar Chart, Pie Chart, Straight Table, and Block Chart. Click on Next.Add the four dimensions –
Country,City,Product, andSalesPerson. For each dimension, turn on Enable Condition and set the following expressions for each of them:Dimension
Expression
Country=Alt(WildMatch(GetFieldSelections(%Dimensions, '|'),'*Country*'),0)City=Alt(WildMatch(GetFieldSelections(%Dimensions, '|'),'*City*'),0Product=Alt(WildMatch(GetFieldSelections(%Dimensions, '|'),'*Product*'),0)SalesPerson=Alt(WildMatch(GetFieldSelections(%Dimensions, '|'),'*SalesPerson*'),0)Add the following three expressions and set Conditional on each of them:
Expression
Conditional Expression
Sum(Sales)
=Alt(WildMatch(GetFieldSelections(%ExpressionName, '|'),'*Total Sales*'), 0)Sum(Orders)
=Alt(WildMatch(GetFieldSelections(%ExpressionName, '|'),'*Order Count*'), 0)Sum(Sales)/
Sum(Orders)
=Alt(WildMatch(GetFieldSelections(%ExpressionName, '|'),'*Avg. Sales*'), 0)On the Style tab, set the orientation to Horizontal.
On the Presentation tab, turn on the Enable X-Axis Scrollbar option and set When Number of Items Exceeds to
8.On the Layout tab, deselect the Size to Data option.
On the Caption tab, turn off the Allow Minimize and Allow Maximize options. Click on Finish.
Add a list box for the four main dimensions. Add a container object for the four list boxes. Add a Current Selections box.
Lay the objects out for ease of use.
There are a couple of things going on here.
First, in the load script, we are setting a HidePrefix variable. Once this is set, any field that has this value as its prefix will become a hidden or system field. The benefit of using this for our dimension and expression selectors is that any selections in hidden fields will not appear in the Current Selections box.
The next thing that concerns us is the conditional functions. I am using the GetFieldSelections function to return the list of values that are selected. We use WildMatch to check if our dimension or expression should be shown. The whole expression is wrapped in an Alt function because if there are no values selected at all, the GetFieldSelections function returns null, so we need to return 0 in that case.