Building a Custom Bubble Chart in QlikView

By Dan Browne, Mon 10 October 2016, in category Business intelligence

qlikview

  
@

You might have seen a custom table in QlikView before. These allow users to build their own reports, selecting a combination of dimensions and measures from a pre-defined list. They can often do the job of multiple tables and encourage user driven analysis – sort of a precursor to Qlik Sense.

But can the concept be applied to charts?

image_1

The bubble chart is a variation of the scatter with an additional measure represented by size. They're useful for spotting correlations and outliers that might have otherwise been missed.

The challenge when creating a custom bubble chart compared to a table is that you require specific inputs for the x axis, y axis and bubble size. Adding all the possible measures with show conditions will not guarantee the correct order. When only one dimension is needed it is also more elegant and easier to maintain a single calculated dimension.

This is how you do it

  1. Create a table of all the dimensions and measures required. I'd recommend doing this in a spreadsheet rather than inline so they can be reused and updated as needed.   image_2

  2. Load these as data islands, making sure to hide the fields from users. The measures need to be loaded three times.

    SET HidePrefix='%';
    
    PICK_Dimensions:
    LOAD [Dimension Name] as [%Dimension Name]
    FROM
    Bubble Chart.xls
    (biff, embedded labels, table is [Dimensions$]);
    
    PICK_MeasuresX:
    LOAD [Measure Name] as [%Measure Name X],
    [Measure Expression] as [%Measure Expression X]
    FROM Bubble Chart.xls(biff, embedded labels, table is [Measures$]);
    

    Repeat for y and z…

  3. Build your chart, adding a calculated dimension and three measures. image_3 image_4

  4. Finally add the four list boxes. You might also want a calculation condition to hide the chart when selections are missing, and colours to show more information about the dimension.