Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
Qlik Insider - Maximize your investment, Join this webinar, May 26th. Register Today
Jennell_McIntire
Employee
Employee

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

75 Comments
Not applicable

Hi Jannell,

Thanks for posting this this is very nice!

0 Likes
7,448 Views
joaquinlr
Specialist II
Specialist II

Hi Jannell,

Great job

0 Likes
7,448 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
7,448 Views
IAMDV
Luminary Alumni
Luminary Alumni
0 Likes
7,448 Views
Jennell_McIntire
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
7,448 Views
robert99
Specialist III
Specialist III

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
7,448 Views
richard_pearce6
Luminary Alumni
Luminary Alumni

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
7,448 Views
Not applicable

Richard,

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

Thanks

Anthony

0 Likes
7,448 Views
richard_pearce6
Luminary Alumni
Luminary Alumni

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
7,448 Views
simongoodman
Creator
Creator

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
7,448 Views