LivingQlik Roots: How-To Create Cyclic and Drill-Down Grouped Dimensions

Featured Image - Cyclic and Drill-Down Grouped Dimensions

Today, I will cover some basics on how to create dimension groups for your QlikView applications. I will also talk about using GetCurrentField to make your charts behave more seamlessly with your groups. This info is must-have for any serious QlikView developer.

What are Grouped Dimensions?

When you build a chart, you typically will use at least one dimension field so that the aggregation in your expression gets calculated for each value in your dimension list. This is a simple concept that we can expand on.

Grouped Dimensions allow the user to “switch out” the dimension with another field. This allows us to re-use the screen “real-estate” without having to create a separate chart for every needed dimension.

Grouped dimensions can be used in any chart. I also use them sometimes in listboxes. They save space if the users understand how they function.

Cyclic vs. Drill-Down Dimensions

In QlikView, grouped dimensions come in two flavors.

Cyclic Dimension – Allows user to choose the dimension at will.

Drill-Down – When a user makes selections resulting in one possible value, the dimension changes to the next dimension field.

The key thing to remember here is that users directly control the dimension in the cyclic style, while the selections drive the displayed dimension in the drill-down style.

Creating a Cyclic Dimension Group

You should know that when you create a dimension group, it can be used in any QlikView object within your application. With that said, they can be either created within your Document Properties or directly in a chart. Go to Settings –> Document Properties –> Groups and select New

Create a group name, choose the radio button for Cyclic Group and then add the fields you want in your group. Note that the order of these fields will represent the order in which users can normally switch between them with the top field being default.

You can also change the label for each field. Simply click on the field and enter a new value in the label field. In this group I renamed my field MonthYear_Short to Month.

Now create a chart using your new cycle group as a dimension. You will see grouped fields at the very top of the dimensions tab.  Once your chart is created, the user can click directly on the yellow circle or can use the pull down to select the desired dimension. Super Cool!

Creating a Drill-Down Dimension Group

Now let’s create a drill-down dimension. This time we will build the group from the chart. Create a line chart and when you get to the dimensions tab, you can select Groups in the bottom left.

Name your group and select the Drill-down radio button. Note that the fields I add are in a specific order from summary level to detail. The fields must be in a hierarchy because of the way the drill-down field operates.

Utilize the new drill-down group as your chart dimension and finish creating your chart. Note that clicking the arrow does nothing at the highest level. Instead, make a single selection in your chart and you will see the dimension change to the next level. At that point you could click the arrow which serves to undo that selection. As you make selections in these fields or other fields that result in only one possible value, your chart will automatically display the next detail level.

Which Group Type is Better?

I am often asked which style is better and my answer is always “it depends on your audience”. Most sophisticated users will prefer a cyclic group because they can directly control the dimension being displayed regardless of the selections.

Users who “just need the dashboard to work” will probably prefer the drill-down groups as they don’t have to do anything to the chart to make it drill down to the next level.

With that said, I have had situations where the client wanted the same chart done both ways.

Professional Tip – Sorting

You might run into a situation where a field in your group is not sorting correctly when in use. Take the example below where we have used Quarter Year. Sorting this alphabetically is wrong and numerically does not work either. Either way, we cannot determine the sort from our normal chart Sort tab.

Open the group and go to Sort Orders. There you can select the field and assign the sort of your choice. In this case we are sorting by expression using a counter field that was added to the data model for this purpose.

This is Where GetCurrentField Comes In

Sometimes, an expression you create needs to interact with the fields you are using for dimensions. This is typical for expressions with aggr functions. So what do you do when your dimension can change? Well, of course, there is a function for that. Let’s cover a few applications of GetCurrentField.

One great application of GetCurrentField is to provide some clarity regarding the dimension currently in use for the chart. I like to modify the title of the chart so it displays the field that is currently being used. As the user cycles or drills though the dimensions, the title of the chart changes to match.

This expression could be entered in the General tab for Window Title.

Resulting in a dynamic title that changes with the drill down.

In another use-case, we have created a line chart that measures profit over time. And instead of using a static dimension like date, I instead created a cyclic group that allowed the user to cycle from Year, to Month, to Date. This is a very common requirement. But in addition, the users require a reference line 75% to the maximum value of the chart and also at the 25% point. This would be pretty simple but how do we know what the highest value is as we cycle from one dimension to another.

Reference lines are created in the presentation tab. Notice that I had to put the GetCurrentField function in dollar-sign-expansion.

Note: If GetCurrentField is not working try putting it in dollar sign expansion

I created both lines and here is the final chart. As I click the cycle button, the reference lines recalculate to the new maximum value.

Final Thoughts

Please note that Qlik Sense does offer some concept of grouping fields but operates a bit differently. And to-date, GetCurrentField is NOT a supported function.

Here is some interesting further reading on the GetCurrentField function.

More Reading –

QlikView Maven – Expression Knows Which Cycle Group Field is Active

Quick Intelligence – QlikView Caption Contest

QlikShare – System/Meta Data Functions in QlikView – Part 1

Happy Qliking!

5 comments… add one
  • Reply Steve Dark May 9, 2017, 5:47 am

    Hi Aaron, nice comprehensive post on the subject. Thanks for putting this together.

    Thanks also for the mention of my post. I can’t believe that the GetCurrentField function still red-underlines. It’s always slightly embarrassing when I’m training people on its use and have to say to just ignore the underline. It’s one thing when variable usage causes error highlighting – that can be explained – but with GetCurrentField it’s just like the product is unfinished.

    Oh well.

  • Reply Tim Benoit May 9, 2017, 5:31 pm

    Hi Aaron,
    It’s funny you mentioned that old QlikView Maven blog post. It was just yesterday that I put a note on my to-do list to re-visit that post and add examples of dollar sign expansion syntax. It is essential if someone wants to use the Aggr function in a chart expression where a cycle group is used in the dimensions.

    I like the layout and topic selections in the LivingQlik blog.

  • Reply Aaron Couron May 9, 2017, 9:37 pm

    Tim ~ I appreciate the comments. I’m just trying to keep my head above water over here 🙂

  • Reply John Cavoulas May 25, 2017, 11:48 pm

    Hi Aaron,
    I now have a newfound interest in the drill-down grouping. I use cyclic a lot but got discouraged with dd many moons ago; When I clicked the drill-down button it just went down…never back up. I either didn’t understand it and set it up wrong, or it was malfunctioning. Anyway, thanks for the clarity.

  • Reply Barry Manis May 25, 2017, 11:57 pm

    Hi Aaron,
    I’m a big fan of using the Dual() function in scripts when building the data model. That way I don’t have to contend with sorting by Expression. Since a text value is associated with a number, Numeric sorting is automatic. And less maintenance.

Leave a Comment

Join us at Living QlikView

Never miss a post.

We Never Spam. NEVER.

Show Buttons
Hide Buttons