This article will describe a method of allowing users to, on the fly, prioritize rows in a pivot or straight table in QlikView!
Although it is not a frequent occurrence, there may be times when it would helpful in QlikView to be able to manually rearrange rows in a pivot or straight table. Since the introduction of QlikView v11, this issue has become much more relevant. QlikView 11 introduced a concept known as "alternate states," which allows an apples-to-apples comparison of several selection states at the same time. For instance, it is now possible to create a state known as "Segment 1" which would display metrics for households with income of $100,000 or more. The user can then create a second state known as "Segment 2," use it to display metrics for households that own two cars, and compare the two states side by side. Finally, the user can add a "Segment 3" to display metrics for households with three children or more. Each state would be independent of the other states.
Viewing each state on its own is quite simple. Likewise, it is even simple to display all three states next to each other in one or more charts. However, what if the user is interested in seeing the flow from one state to another in a dynamic way? Meaning, suppose that the user wants to analyze how many people who are in Segment 1 are also in Segment 2, and then see what percent of that intersection is in Segment 3. No problem—this is easily accomplished through a combination of set multiplication and/or subtraction. But what if the user wants to change the order of the flow to Segment 3 >> Segment 1 >> Segment 2? Without the ability to dynamically resort his segments, he would need to actually modify the chart expressions to change the order that the sets are compared—quite a tedious process. With dynamic prioritization, however, it's as simple as this:
To see techniques used by this example, download the free app here: DynamicPriority.qvw. Small caveat: this solution is intended for sorting tables with no more than 10 rows; because both the arrow button actions and the chart expressions need to be created one at a time, the procedure would get tedious for tables with more rows than this. Other than that, happy Qliking!
