Everyone knows that you cannot define buckets dynamically in QlikView, right? Not quite—this article will describe precisely how to give your users this ability!
What exactly do we mean by "dynamic bucketing"? Simply put, this is the ability to group data into broader categories in order to enable users to see high-level trends that are meaningful. For example, a typical bucketing implementation would be to group individuals' ages into pre-defined age groups (e.g. 0 - 5 years old, 6 - 12 years old, etc.). Bucketing is widely used in QlikView applications, with the industry standard being to interview business users in order to get the exact bucket definitions that the business needs. But what if users want the ability to define these buckets on-the-fly, without the need to turn to the QlikView developer for each revision? Many developers do not realize that, in fact, this is possible with QlikView!
Let's take a look at how this solution would work, using age bucketing as an example:
Creating a Place for Users to Enter Definitions
Before we begin, I would like to note that the solution described in this article will currently only work in QlikView Desktop or the IE Plugin. This is because this first step relies on a simple macro that is currently not supported in the AJAX client. However, as Qliktech's stated goal is to eventually provide 100% parity between AJAX and QlikView Desktop, it is quite likely that the solution will eventually gain support in AJAX, as well.
First, we are going to create a nice input box for users to enter bucket definitions. Because a regular QlikView input box is only a single line, we should create an InputBoxEx object, which will give users a nicer multi-line canvas:
This box is created through a simple VBScript macro that populates a variable called "vAgeBucketDef":
Let's give users the ability to enter buckets in the following formats: (1) X - Y, and (2) Z+ (to define the catch-all bucket at the end):
Parsing Bucket Lower and Upper Limits
In order to be able to determine that an age falls into a particular bucket, we need to create 2 new variables that will determine the lower and upper age limits of each bucket. Let's call these variables "vAgeBucketLower" and "vAgeBucketUpper". The definitions of these variables appear below. The logic in each definition uses functions subfield() and valueloop() inside of a concat() aggregation. Using these functions inside of an aggregation function ensures that we cycle through each bucket when we check each age to see where it should be bucketed. Also note that it is essential that the concatenation appear in the same order as the original bucket definitions entered by the user. This is because we will use the same valueloop() value to pick the correct bucket when we've found a match. For purposes of this example, we are going to assume that the user has entered them in ascending numerical value (if this is not the case, then another intermediary variable would need to be created to reorder the buckets).
vAgeBucketLower
vAgeBucketUpper
The result of these variables, using the buckets we created above is:
Using Advanced Aggregation to Assign Buckets
Now that our buckets and lower/upper limits have been defined, we need to actually assign each age to a particular bucket. Doing so requires the use of advanced aggregation—function aggr(). Let's create a new variable called "vAgeBucketAggr" which we will populate with a dual() function (so that the buckets can be sorted numerically). This variable will then be used in our subsequent aggr() function:
vAgeBucketAggr
Putting It All Together
All of our variables have now been defined and we are ready to build our front-end objects. In order to use the logic written above, we will need an expression that we can either put into a calculated dimension or display as a listbox for user selection. The expression will simply be:
Because this expression returns a dual value, we can sort it numerically. The final result may look something like this:
To see the solutions discussed in this article in detail, feel free to download the following QVW: DynamicBucketing.qvw
Hi,
I have created a document as per your above example but when i click on define bucket button it shows the input field with values with continous string and not in 5 lines as shown in your demo, could you pelase help me out what could be the reason
Dhrumil,
Assuming you implemented the “InputBoxEx” macro above, the nice multi-line popup will only work in IE Plugin or QV Desktop as I write at the beginning of the article. Are you by any chance trying to open in AJAX? What I typically do is use the ClientPlatform() function to hide the “Define Buckets” button when the application is opened in AJAX mode, so that bucket definitions would be set when the multi-line box is available only.
Another possibility, if you need to enable bucket definition setting in AJAX mode, is to change the parsing parameters from chr(10) to ‘;’ and require users to separate values with a semicolon. This won’t look quite as nice, since it won’t give users a multi-line interface, but should work.
Regards,
Vlad
Pingback: Better QlikView Trainings – Infinity Insight Blog
Excellent
Great Stuff! Very easy to use Thanks