How to Build a Cycle Group in Qlik Sense

One of the neat features in Qlik View that has not found it’s way into Qlik Sense is the Cycle Group. Alternative Dimensions are nice, but only affect one visualisation at a time. Here is how you can create a cross-visualisation cycle.

The Challenge

As Sense has matured more and more of the gaps between it and the features we were spoiled with in QlikView have come across into Qlik Sense (this is even more true if you turn to extensions, both free and fully supported). There are still some annoying omissions with Sense “out-of-the-box” though, especially if you are using Qlik Sense in the Cloud – where extensions are not yet supported.

One thing that I made a lot of use of in QlikView was the Cycle Group. This is very useful for allowing users to self-serve their analysis, by picking which fields to view measures over. What was particularly useful with the Cycle Group is changing it on one chart then changed it in all others simultaneously – so you could view multiple measures over one dimension in different charts. The toggle button technique I blogged on previously could be used for this, but has the drawback that it is not so easily extendable and requires an extension (so Qlik Sense Cloud is out).

Fortunately, there is a solution which I have been using for some time – and I will share it with you now.

The Data and The App

As we are currently in the closing stages of Wimbledon (it may be all over by the time I hit Publish) I thought I would find some tennis related data. As is often the case; Wikipedia came through on that front, with this table of Mens Singles Grand Slam Finals.

https://en.wikipedia.org/wiki/List_of_Grand_Slam_singles_finals

As with many of my other examples the example related to this tutorial can be downloaded from Qlik Community here: https://community.qlik.com/t5/Member-Articles/Create-a-Cycle-Group-in-Qlik-Sense-without-extensions/ta-p/1480800

As ever, likes shares and comments are always welcome on these apps if you find them helpful.

Building The Cycle

This solution will work over any data set, but just as with QlikView Cycles you do need to ensure that every dimension value you add works with every measure you run over it.

If you want to follow the example I have here you will need to connect to and load the Wikipedia Grand Slam data I reference above. Create a new Web connection, called GrandSlam in sense and paste in the URL.

The script to load the data is then this:

GrandSlam:
LOAD
    1 as Count,
    1 as Won,
    0 as Lost,
    "Year",
    Tournament,
    Replace(Replace(
    	SubField(Tournament, ' ', 1),
        '.', ''), 'as', '') as [Tournament All Era],
    if(index(Tournament, 'Open') > 0
    	or Tournament = 'Wimbledon', 'Open', 'Pre-Open') as Era,
    'Won' as Result,
    Winner as Player,
    Winner,
    null() as [Runner-up]
FROM [lib://GrandSlam]
(html, utf8, embedded labels, table is @2);

CONCATENATE(GrandSlam)
LOAD
    1 as Count,
    0 as Won,
    1 as Lost,
    "Year",
    Tournament,
    Replace(Replace(
    	SubField(Tournament, ' ', 1),
        '.', ''), 'as', '') as [Tournament All Era],
    if(index(Tournament, 'Open') > 0
    	or Tournament = 'Wimbledon', 'Open', 'Pre-Open') as Era,
    'Lost' as Result,
    [Runner-up] as Player,
    null() as Winner,
    [Runner-up]
FROM [lib://GrandSlam]
(html, utf8, embedded labels, table is @2);

As I mentioned though, you can use this technique with any data set that you have.

The first thing you need to do is build a table, in your load script, which contains all the cycle dimension fields. This table is to have a dimension and a sort order, and can be built using an inline load; like this:

Dims:
LOAD
    Dim,
    RowNo() as DimSort
INLINE [
Dim
Player
Tournament
Tournament All Era
Era
Year
Winner
Runner-up
Result
];

If you are not familiar with INLINE loads, they are a way of typing data straight into your load script. The field name and all values are typed straight in (or copied and pasted from a previous LOAD statement). The sort order is created on the fly, based on the order of the values in the INLINE section. If you have lots of fields to pick from, or you want to delegate management of the cycle list, this can come from a spreadsheet or database instead.

Save your load script and perform a reload.

Now, go into the App Overview, and then on into the sheet you wish to place your cycle group on to. Click the Edit button, so we can do this. Here we are going to drag a the Dim field onto the page, click the Fields button and drag dim into space. You will probably need to resize it to be smaller.

Now we want to change the legend on the Filter, by setting the Title (under Select Dimension Field in the right hand accordion) to:

=if(GetPossibleCount(Dim) = 1, 'Dimension: ' & Only(Dim), 'Select Dimension Field')

This will show the dimension name, if there is one selected, or prompt the user to select a dimension if they have not yet done so. We will use the dimension we have given most precedence with the sort if there is more than one possible value – this is explained soon.

We also want to ensure that the values in the drop down appear in the same order as we had them in our load script. This is why we added the Dim Sort field, and that needs to be entered in as an ascending sort order, with this code:

=min(DimSort)

The filter should look like this:

We now want to create a couple of variables. Whilst in Edit mode look for the variable symbol at the bottom left of the page, hopefully the creation of the two variables will be intuitive from there:

The first variable is to find the lowest possible sort number associated with a dimension, and the other is to return the dimension name itself.

vMinDimSort:

=min(DimSort)

vDim:

=minstring({<DimSort={$(vMinDimSort)}>}Dim)

You will note that the first variable is nested in the second variable, and is used to pick out the dimension we have given precedence to with the sort order, rather than the first dimension alphabetically.

Now that the variables are created, we can use them in our first chart. To do this drag a table object onto your sheet:

Once the table is on the sheet you will want to add your dimension to it. This includes a reference to the second variable we constructed and should read like this:

=[$(vDim)]

The dimension name, sadly, can not be derived from an expression (why, Qlik, why?) so we have to put a generic label in here, I’ve gone for Dimension [Note this is no longer the case, you can create a Dimension label of =’$(=vDim)’]. I’ve heard a rumour that dynamic label and expression labels will arrive at some point soon – I hope this is the case as they are essential for decent point-in-time reporting. Anyway, the dimension settings should look like this:

For the Grand Slam Tennis example you will also want to tick to remove nulls from this dimension (using the tick box) due to how each match is split onto two rows with null values in the Winner and Runner-up columns. Also, it is a good idea to set the alignment explicitly, so you don’t get years aligned to the right and other dimensions to the left.

You now need to enter in the expressions that you want to run over your dynamic dimension. You can create each of these as Master Items and then simply drag them onto the table. Master items are under the chain link item on the left. Select this, then measures, then Create New, like so:

The master item for matches played should look like this:

The master measures I have created are:

Played
Count(DISTINCT Tournament & Year)

Won
Sum(Won)

Lost
Sum(Lost)

% Won
Sum(Won) / Sum(Count)

% Lost
Sum(Lost) / Sum(Count)

Once all Master Items are created you can drag them onto your table and configure them.

We can even set up our dimension as a Master Item (under Master Items, Dimensions) so we can simply drag that onto further charts:

Once all of the measures have been dragged onto the table, the numeric types have been sorted (you should be fine with the counts, but the percentages will need setting under the Measure settings) you should have a table like this:

Using the Filter Box we added earlier on we can change the view so that it shows the values by different dimensions.

I’ve then extended my app to have charts for four of the measures, and the beauty of this solution is that when the dimension changes for one object it changes for all of them:

If you are following this example then hopefully you will be able to create something similar, or you can just download my finished app from here.

Conclusions

Hopefully that has given you everything you need to create your own cycle filter groups in Qlik Sense, against your own data. The technique can also be employed in QlikView, but generally I would use the native Cycle Group as that works well. Having the fields listed in a List Box can be more intuitive if you have users that you are not looking at training.

An extension of this technique that I also use extensively, particularly in QlikView, is to load a list of expressions from a spreadsheet into an app and then provide a List Box to select the expression. This way you can have multiple charts on a page, with different dimensions, all of which can show the same expression that the user has selected – kind of the opposite of the example above. This can be further expanded to allow selection of multiple expressions, perhaps to do ratios or a scatter chart. It’s slightly more involved to get expressions working rather than dimensions – perhaps that is one for a future blog post!

Thanks for reading. As ever please feel free to comment, share etc.