Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Customizable Straight Table

In many of the demos that I have done, I have added a customizable straight table or ad-hoc report where the user can select the dimensions and measures that should appear in the chart.  This is often helpful and can replace multiple detailed straight tables in an application.  Creating the customizable chart involves simply loading the dimensions and measures that the users can pick from and then adding a straight table with conditional dimensions and expressions.

 

In the Call Detail Record Analysis demo a customized report was added to the application.  This is how the ad-hoc report looks with some dimensions and metrics selected.

 

report.png

 

The steps taken to build this report were:

 

  • Load dimensions and metrics that should appear in the list box for the user to choose from.  In this example, this was done by adding two inline tables with the script below.

 

inline tables.png

 

  • Then list boxes for _dimension and _metrics were added to the report sheet with the LED Check boxes selection style.

 

     led style.png

 

  • Next a straight table is added to the sheet with the following conditional calculation:

 

     calc condition.png

        The chart will be displayed if at least 1 dimension and 1 metric are selected from the list boxes.

 

  • Next the dimensions were added to the table.  Each dimension in the list box was added to the chart with a conditional expression so that the dimension is only displayed if it is selected from the Dimensions list box.

     dimensions.png

 

  • The same was done for each measure listed.

 

     expressions.png

 

 

In five easy steps, a customizable straight table or report can be added to your QlikView application providing users the ability to display a report with the data they want to see at the time.  This gives the user more flexibility and reduced the need to add detailed straight table charts to the sheets in the application.

 

You can read more about this topic in my Technical Brief which has more detailed step-by-step instructions.  Have fun!

 

Thanks,

JMC

74 Comments
bme
New Contributor III

We've used this technique in a number of places though with a slight modification. We load the dimensions and metrics from a spreadsheet. This spreadsheet is loaded as two island tables, one for dimensions and one for metrics. Then in addition to using references to the list boxes for show conditions, we call back to the island table for the dimension and expression formulas as well. Basically the entire table made of calculated values, dimensions, labels, expressions, showconditions, etc.

Dimension

=$(=SubField(Concat({1}FieldName,',',FieldIndex('FieldName',FieldName)),',',1))

Metric

$(=SubField(Concat({1}metric_Expression,'|',FieldIndex('_metric',_metric)),'|',1))

In this way we can migrate the tool from one application to another very easily. Paste in a few objects, a chunk of script, and update the spreadsheet and it's all ready. A highly end user customizable report. Stack that with shared bookmarks and our end users have never been happier. A big thanks to kyle_frye and DI Squared for helping to set up the first one!

1,184 Views
jdvermeire
Contributor

Nice synopsis on doing ad-hoc tables in QV.

I'll throw my hat in on an alternative method for how I've done the same sort of thing in the past.

I like to use an enumeration as the key in the metric and dimension island tables:

{1,2,4,8,16,32,64,...}

This way each key only occupies one bit in the integer.

I then add two variables that sum the metric and dimension keys, respectively, only if a selection is made:

SET v.metric='=If(GetSelectedCount(metric_name), Sum(metric_key))';

SET v.dimension='=If(GetSelectedCount(dimension_name), Sum(dimension_key))';

Finally, for the conditional expressions in the chart, I use the "bitand" function to test for the selected metric or dimension:

Conditional: v.metric bitand 4

The above would only show if the third metric is selected in the list box.

Theoretically, this should use less processes to calculate since bitand is a native operation for a PC and SUM is simple to calculate.

Let me know if I need to expand on this to explain it a little clearer.

-Jeff

1,184 Views
neilhepburn
New Contributor II

We're all doing the same thing here, but I'll share with you the specifics of my approach, as it may have certain advantages over other approaches described.

As usual, I create two island tables: First for dimensions and the second for measures.

Tip #1: use a hide prefix or suffix so these selections don't show up in Current Selections.

Tip #2: use REPLACE for partial reloads (useful for adding new measures without having to do full reloads - partial reloads should only be used for testing, since the Load order isn't what I normally Expect

Tip #3: Order the dimensions and measures according to user query patterns.  Namely, put most frequently accessed dimensions and measures near top of list.  Then, in your List Boxes short by "Original Load Order"

Example:

[Dimension Selector]:

Replace

LOAD * Inline [

Dimension Name%

Example Dimension Field

Example Second Dimension Field

];

[Expression Selector]:

Replace

LOAD * Inline [

Expression Name%

Min Trans

Max Trans

Median Trans

];

You can then define two variables, which are both parameter driven:

SET ShowDimension=if(SubStringCount(',' & GetFieldSelections([Dimension Name%], ',', 100) & ',', ',' & $1 & ',') > 0, True(), False());

SET ShowExpression=if(SubStringCount(',' & GetFieldSelections([Expression Name%], ',', 100) & ',', ',' & $1 & ',') > 0, True(), False());

Then in each of the dimension and expression Conditional expressions, set the expression as follows to bind a given dimension or measure to the respective Dimension

e.g.

$(ShowDimension('Example Dimension Field'))

For expressions

e.g.

$(ShowExpression('Min Trans'))

With this approach it is relatively easy to specify and maintain bindings between dimension selectors and their respective Chart Dimensions, and between measures and their respective Chart Expressions.  I like using the parameter driven variable since it is much more readable and less error prone when you're in the Chart properties wiring up the chart.

One last tip: I allow users to fast change between Pivot Table and Straight Table, and leave the default as Pivot Table. This gives the best "drag and drop" experience, and allows users to easily create cross tab reports (e.g. months as columns).  I tell users to switch to the Straight Table if they want to perform interactive sorting.

I have created a sample QlikView document which you can find here:

http://community.qlik.com/docs/DOC-5811

1,184 Views
carbal1952
Contributor II

This post is amazing !

Thanks Jennell

CB

0 Likes
1,184 Views

This post is very useful and we would like other users to post the different methods they are using...

0 Likes
1,184 Views
jochem_zw
Contributor II

Hi Ben do you have a working example of your approach?

0 Likes
1,184 Views
Not applicable

Hi Jennell

it is possible to attach qvw doc ? so that i cab better understand how it your approach work ?

0 Likes
1,184 Views

Ben Myers, jdvermeire and neilhepburn

Can you please upload a sample apps....? Very thankful to you...

0 Likes
1,184 Views
Employee
Employee

Hi Paul,

The QVW I refer to is the Call Detail Record Analysis demo which is available for download from demo.qlik.com.

Thanks,

Jennell

0 Likes
1,184 Views
Sajid_Mahmood
Contributor

Hi

Can someone attach the sample file with alternative method used by Neil and Jeffrey.

0 Likes
1,184 Views
michael_gardner
Contributor III

QlikView How To (or Tips & Tricks) Application

You can find a working example done slightly different in the Quick Report section of the file attached to the above article.

0 Likes
1,184 Views
neilhepburn
New Contributor II

Further to my last e-mail (and in response to other commentators)  I have created a simple QlikView document which illustrates how I put my technique into practice.

http://community.qlik.com/docs/DOC-5811

0 Likes
1,184 Views

Thanks Neil...

0 Likes
1,184 Views
jason_michaelid
Honored Contributor II

EDIT: I have uploaded a document explaining this - Customisable Straight (and Pivot) Tables - more food for thought

Hi all,

Seeing as this is a hot topic right now I'll whet your appetites with an even more flexible approach and at the same time commit myself to writing the doc I have intended to write for a while now, within the little while...

For now here's the basic(ish) premise of my preferred approach and the expansions over the above solutions...

The one challenge with the above approach 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 as described above. 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 a any one time?

The other thing to consider is the left-to-right order the user might want his chart. With the above approaches the only way to adjust the expression order is with a macro which I try to avoid if possible.

The solution 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()

I will provide full details and examples soon, but for now, for each customisable chart, load a data island of dimensions and a separate data island of expressions.

Dimension:

%Dimension_Name - this will be the list the user sees

%Dimension_Explanation - can be used in comments or elsewhere to provide colour to the user

%Dimension_Group - enables grouping of dimensions for the user

%Dimension_Order - 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 colour 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_Format - a default format for the expression e.g. #,##0.00;-#,##0.00

%Measure_Order - 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 _Order 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_Order,1) & ']') for the first dimension

$(='[' & FirstSortedValue(%Dimension_Name,%Dimension_Order,2) & ']') for the second dimension

etc etc and

$(='[' & FirstSortedValue(%Measure_Name,%Measure_Order,1) & ']') for the first expression

$(='[' & FirstSortedValue(%Measure_Name,%Measure_Order,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

If you then give the user the ability to change the _Order fields (remember they are input fields) then 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) along with the more detailed post later.

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

This is a great thread - hope I have added something useful to it!

Laters,

Jason

Calibrate Consulting.

EDIT: I have uploaded a document explaining this - Customisable Straight (and Pivot) Tables - more food for thought

1,184 Views
neilhepburn
New Contributor II

Look forward to seeing an example of this Jason - providing more control over column order would be great!

For now, users just manually move columns around, but a default order would help.

0 Likes
1,184 Views
kalyandg
Contributor II

hi Jennell,

very nice post

0 Likes
1,184 Views
Not applicable

Hey guys, 

I am given a task to calculate the frequency of calls across a territory. If the rep called a physician regarding the sale of the product 5 times, then frequency is 5 and HCP count is 1....I generated frequencies from 1 to 124 in my pivot table using a calculated dimension which is working fine. But my concern is :

My manager wants frequencies till 19 in order from 1..2..3..4...5..6.....19...

And from the frequency 21-124 as 20+.

I would be grateful if someone helps me with this.....Eager for the reply....

0 Likes
1,184 Views
jason_michaelid
Honored Contributor II

I think this belongs in a question of its own, Abhi...

0 Likes
1,184 Views
jason_michaelid
Honored Contributor II

Seriously Abhi - please do not pollute unrelated threads. Please put into a question of its own.

0 Likes
1,184 Views
Not applicable

Jason,

Thanks for taking the time to add your method.  I am excited to see your update when you have the time.

1,184 Views
Not applicable

Hi Jannell,

Thanks for posting this this is very nice!

0 Likes
1,184 Views
joaquinlr
Valued Contributor II

Hi Jannell,

Great job

0 Likes
1,184 Views
Not applicable

Hi Jannell,

Just a general comment.  I used and it is working great.  Thanks for the work and great technical pdf.

0 Likes
1,184 Views
IAMDV
Honored Contributor II
0 Likes
1,184 Views
Employee
Employee

Hi Petere123,

There is a setting in QlikView that will export current selections whenever you export to Excel.  To do this, go to Settings > User Preferences... > Export tab > In the "Selection Stamps in Exports" section, check the "On BIFF Exports" check box. 

In the example I was showing, this would not work because I am using HidePrefix to hide the fields used for the dimensions and metrics in the chart therefore they are not showing up in the Current Selection box.

Hope this helps.

Jennell

0 Likes
1,184 Views
robert99
Valued Contributor II

Thanks This is a great new feature

But it never worked as it should at work for the metrics. (It either selected metrics when it should not or would not select metrics)

Until

  • I removed the metricNo (and just used

          load inline [_metrics

          calls etc    ];

and

  • used this SubStringCount (for both dimensions and metrics)

=SubStringCount('|' & Concat(distinct Dimension, '|') & '|', '|Calls|')  etc

I found this solution on this thread

http://community.qlik.com/docs/DOC-5404

0 Likes
1,184 Views
richard_pearce6
Valued Contributor

Hi Jason,

As promised I've posted an example application which expands on this idea. One of the key benefits of using FirstSortedValue() is that users can change the order of expressions by loading the SortWeight (%Measure_Position) as an inputfield.

Works perfectly and to add further usability here is the example of changing the SortWeight (%Measure_Position) using action buttons and vb_script.

http://community.qlik.com/docs/DOC-6223

Regards

Richard

qlikcentral.com

0 Likes
1,184 Views
Not applicable

Richard,

You linked document seems to be restricted.  Who can access this document?

Thanks

Anthony

0 Likes
1,184 Views
richard_pearce6
Valued Contributor

Hi Anthony,

I edited the document shortly after posting my comment and its now awaiting moderation. Hopefully it will be available soon.

Richard

0 Likes
1,184 Views
simongoodman
Contributor

This looks like a very useful feature, so I tried a simple example and found an interesting oddity.

QV_Metrics.jpg

If Metric 'Target Net Margin' is selected both Target Net Margin and Net Margin expressions are selected in the chart. I puzzled over this. Then renamed  'Target Net Margin' to 'Target Margin' and this solved the problem. It seemed the similarity of the two labels (re:expression/metric) was the cause. Has anyone noticed this and would you have an explanation?

I have an additional question and I hate to admit this, when no metric has been selected the chart displays a default 'Calculation condition unfulfilled'. Where can this be changed in the chart?

Thanks

Simon


0 Likes
1,184 Views