Qlik Community

QlikView Documents

Documents for QlikView related information.

Customisable Straight (and Pivot) Tables - more food for thought

jason_michaelid
Honored Contributor II

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)


Hello everyone,

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 any one 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.

Dimensions:

%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:

%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

etc etc

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!

Cheers,

Jason

Calibrate Consulting.

Attachments
Comments
Luminary
Luminary

Great post Jason. Pivot Tables & Straight Tables on steroids. Especially changing the column positions based on the Input field is very thoughtful.

This is very powerful technique. (5 Star!)

Cheers,

DV

Partner
Partner

Very very good example... thanks for your share app.

Partner
Partner

Thank you, Jason.

That article is a thought provoking indeed.

I wonder do you use this metadata-driven approach only for that sort of highly dynamic charts?  If not - how that approach works on level of  the whole user application?

I myself have mixed feeling about using external storage for chart's formulas without transformation them into variables. I guess we losing some power of composability in that.


jason_michaelid
Honored Contributor II

Hi Vadim,

"I myself have mixed feeling about using external storage for chart's formulas without transformation them into variables. I guess we losing some power of composability in that."

I'm not sure I understand you. Are you saying that rather than store the expressions in a data island you would put them directly into variables?

Jason

0 Likes
jason_michaelid
Honored Contributor II

Sorry - didn't answer the main question!  I do use this meta-data approach a lot. I find it

  • Simplifies the script
  • Allows easier admin by trusted IT people who may not be QV experts
  • Allows a level of business-user control without compromising application integrity

Unfortunately, a nice configuration database is not normally an option so Excel ends up being the tool of choice for storing the configurations. However this can be secure enough if planned correctly.

Jason

0 Likes
Partner
Partner

Jason Michaelides написал(а):

I'm not sure I understand you. Are you saying that rather than store the expressions in a data island you would put them directly into variables?

Exactly.

Our standard approach until very recently was:

  • Manage external table with expressions, labels and comments
  • Load labels and comments from that table into data island
  • Load formulas from that table into variables

So typical chart expression in our appplication has something like

  • $(Sales)  - for its Definition
  • =$(GetLabel('Sales')) - for its Label
  • =$(GetComment('Sales')) - for its Comment

Currenlty we load Comments, Labels and additional attributes into related variables

So typical chart expression in our appplication now have something like

  • $(Sales)  - for its Definition
  • =Sales.Label - for its Label
  • =Sales.Comment - for its Comment
  • =Sales.BackgroundColor - for its BackgroundColor

I describe our environment here: InQlik Expression Editor in QlikView Deployment Framework environment

Storing formulas into variables provide composability

For example, given `SalesCurrentMonth` and `SalesPrevMonth` expressions we may define variable for trend as

`$(SalesCurrentMonth)/$(SalesPrevMonth)`

I guess it would be hard to achieve if all formulas are just fields in island table.

jason_michaelid
Honored Contributor II

I can't get to your Doc - says I am "unauthorized" to access it...!

Vadim Tsushko wrote:

I guess it would be hard to achieve if all formulas are just field in island table.

I don't think so - I think it is easier.  No reason a row in the data island of expressions can't have a Name "Current Mth vs Prev Mth" and an Expression "$(SalesCurrentMonth)/$(SalesPrevMonth)". Then the selection of this expression can be bookmarked. Allows more flexibility and reduces the number of variables (and variables can be difficult to manage).

On a slightly separate note - I'm sure you've already done this but make sure you are conscious of when variables are calculated. Do not use "=" in the variable definition, rather use $(=) in the chart expression to ensure it is only calculated when required.

Jason

Partner
Partner

I guess you did not join QlikView Deployment Framework Group yet 

Highly recommend it to you.

Meanwhile there is same post at -  inqlik . github . io  /2014/02/expression-editor/ 

0 Likes
Partner
Partner

Vadim Tsushko wrote:

I guess it would be hard to achieve if all formulas are just field in island table.

I don't think so - I think it is easier.  No reason a row in the data island of expressions can't have a Name "Current Mth vs Prev Mth" and an Expression "$(SalesCurrentMonth)/$(SalesPrevMonth)". Then the selection of this expression can be bookmarked. Allows more flexibility and reduces the number of variables (and variables can be difficult to manage).

Well, you can use regular variables within your expressions. But your expression can be potentially usefull for some next expression - and then no luck, I believe.

Say sales for currenth month are defined only as formula in data island, and so sales for previous month.

To use them in `Current Mth vs Prev Mth` we should copy and paste definitions from originals.

Or use same `lookup with macro expansion` techic as in UI?

0 Likes
jason_michaelid
Honored Contributor II

Don't forget I am proposing the chart expressions use FirstSortedValue() so are entirely dependent on the expressions selected in the data island.  You can have:

Measure
  Name
ExpressionPosition
Sales (Current Month)Sum(Sales)1
Sales (Prior Month)Sum(Sales*Flag_PriorMonth)2
Sales (Curr vs Prior Mth)Sum(Sales)/Sum(Sales*Flag_PriorMonth)3

The chart expressions are simply:

$(='[' & FirstSortedValue(Expression,Position,1) & ']')

$(='[' & FirstSortedValue(Expression,Position,2) & ']')

$(='[' & FirstSortedValue(Expression,Position,3) & ']')

Or am I misunderstanding you?

Jason

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-03-03 06:02 PM
Updated by: