Customisable Straight (and Pivot) Tables - more food for thought
EDIT 25th November 2014!!
Guests to the inaugural #QlikDevGroup meeting tonight - the flexi-chart demo goes waaaayyy beyond this!!
(updated doc and code will made available to everyone soon)
A few weeks back, Jennell McIntire posted a very useful blog article entitled “Customizable Straight Table”. This excellent post received a lot of comments and I chimed in with how I have previously taken this concept to an even more customisable level using FirstSortedValue(). I promised I would post an example soon, so – after a bit of a delay – here it is….
Please see the attached Northwind.qvw file (ah, the good old Northwind data set!) as well as DimsAndMeasures.xlsx and I have uploaded the Nothwind Access db too so you can fully reload.
The main challenge with the other suggested approaches is that all expressions that might possibly be required must be set up on the chart(s) in advance, then conditionally enabled based on data island selections. Any new expressions must be added to the chart itself which can be cumbersome as the chart can become difficult to maintain and can also get bloated (I have no empirical evidence, but I'm pretty sure the more expressions that are added the slower and more fragile the chart becomes, even if not many are enabled at a time). What if you have hundreds of possible dimensions and measures for the user to choose from, but maybe only 20 or so are required at anyone time?
The other thing to consider is the left-to-right order the user might want his chart dimensions and measure to be in. With the other approaches the only way to dynamically adjust the expression order is with a macro which I try to avoid if possible and anyway the macro would again need to include all possible dimensions and measures.
The solution I have successfully used is the wonderful function FirstSortedValue(). I encourage you to view the excellent 2-part tutorial on this function given by Deepak Vadithala (DV) on QlikShare here - FirstSortedValue()
You should be able to understand the attached qvw easily enough. The basic concept is that for each customisable chart, load a data island of dimensions and a separate data island of expressions.
%Dimension_Name - this will be the list the user sees
%Dimension_Explanation - can be used in comments or elsewhere to provide information to the user
%Dimension_Group - enables grouping of dimensions for the user
%Dimension_Position - default left-to-right order for the expressions
%Measure_Name - this will be the list the user sees
%Measure_Explanation - can be used in comments or elsewhere to provide information to the user
%Measure_Group - enables grouping of measures for the user
%Measure_Expression - this is the expression needed to calculate this measure (no '=' sign at the start)
%Measure_Position - default left-to-right order for the expressions
When you load these tables (I often load from a spreadsheet as it is easier to maintain) be sure to declare the two _Position fields as INPUT FIELDS in the script.
Now, add (e.g.) 20 dimension fields and 20 expressions to your chart, but instead of an actual field, use:
$(='[' & FirstSortedValue(%Dimension_Name,%Dimension_Position,1) & ']') for the first dimension
$(='[' & FirstSortedValue(%Dimension_Name,%Dimension_Position,2) & ']') for the second dimension
etc etc and
$(='[' & FirstSortedValue(%Measure_Expression,%Measure_Position,1) & ']') for the first expression
$(='[' & FirstSortedValue(%Measure_Expression,%Measure_Position,2) & ']') for the second expression
For the enable conditions, Dim1 just needs GetSelectedCount(%Dimension_Name) >= 1, Dim2 just needs GetSelectedCount(%Dimension_Name) >= 2 etc and Expr1 just needs GetSelectedCount(%Measure_Name) >= 1, Expr2 just needs GetSelectedCount(%Measure_Name) >= 2 etc
You can see in the MEASURE POSITION and DIMENSION POSITION charts I have given the user the ability to change the _Position field values (remember they are input fields - hover over the right hand side of the position cell and click on the arrow to put the cell in edit mode). Now, not only can the user choose from a list of potentially hundreds of expressions, they can order the columns too. Richard Pearce has a great way of making this step very interactive and simple for the user. I'll post it (with his permission) soon.
NOTE! You must never have two input field values the same or the FirstSortedValue() will not work. I have added a simple conditional background colour to the charts to alert the user if he does this.
You will need a simple macro to allow the users to reset the input fields to their default values as changes persist through reloads and even sessions, I think.
And the best thing? The user can then save their selections as a bookmark (with the Input Field Values also saved) and call it back at any time 🙂
It was a great topic - hope I have added something useful to it!