Creating dynamic ad hoc analysis in QlikView

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.

Getting ready

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
];

How to do it…

These steps show you how to create dynamic ad hoc analysis in QlikView:

1. 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.

2. Create a new bar chart.

3. 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.

4. Add the four dimensions – Country, City, Product, and SalesPerson. 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*') ,0
   Product	=Alt( WildMatch( GetFieldSelections(%Dimensions, '|'), '*Product*') ,0)
   SalesPerson	=Alt( WildMatch( GetFieldSelections(%Dimensions, '|'), '*SalesPerson*') ,0)

5. 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)

6. On the Style tab, set the orientation to Horizontal.

7. On the Presentation tab, turn on the Enable X-Axis Scrollbar option and set When Number of Items Exceeds to 8.

8. On the Layout tab, deselect the Size to Data option.

9. On the Caption tab, turn off the Allow Minimize and Allow Maximize options. Click on Finish.

10. Add a list box for the four main dimensions. Add a container object for the four list boxes. Add a Current Selections box.

11. Lay the objects out for ease of use.

    

How it works…

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.

There's more…

There is a lot of scope for this to be extended. I have only included one chart here and you could use multiple charts and have multiple different options for selections.