Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
Jennell_McIntire
Employee
Employee

Four years ago, I wrote the Customizable Straight Table blog illustrating how you can build a custom table in QlikView.  Last year, I wrote the Custom Report Extension blog that shows how to use the Climber Custom Report in Qlik Sense.  Today, with the help of Patric Nordstrom and Jason Michaelides, I am going to blog about how you can build a custom report in Qlik Sense.  With continued enhancements to the Qlik Sense table such as column show conditions and the ability to change the column order of a table, building a custom report in Qlik Sense is possible right out-of-the-box.

 

Patric built the attached Qlik Sense app that illustrates how to build a custom report in Qlik Sense.  In this blog, I will review the steps needs to build your own report.  Below is an example of how the report looks with some dimensions and metrics selected.  On the left there are two filter panes, one for the dimensions and one for the metrics, that can be used to select the columns you would like to see in the report.  Then there is the table which by default will be empty and will prompt the user to select the dimensions and metrics.  In this example app, the user is encouraged to select no more than 5 dimensions and 4 metrics.

table.png

Let’s start by looking the script.  You would load your data as usual but what you will need to add to the data model is a data island for the dimensions and for the metrics.  In the script below, you can see how Patric did it, providing very clear details of each dimension and metric.  The first field in each table is the dimension/metric grouping, the second field is the dimension/metric name as it will appear in the filter panes.  The third field is a number to represent the default order of the dimension/metric columns in the report and the last field is description data about the dimension/metric.  The KPIs table has a fourth field that shows the formulas for the metrics.

script.png

Once the app is loaded with the data islands, you can begin to build the custom report.  You will need to add two filter panes and a table to your sheet.  The Dimension filter pane uses the Dimension field and the Metric filter pane uses the Metric field.  In the table, you will add 5 dimension columns and 4 measure columns since that is what is encouraged in this example.  Now here is where Patric used Jason’s FirstSortedValue trick.  For the first dimension, enter the text below in the Field.

field.png

And this in the Label:

label.png

So, you may be wondering what the expression is doing.  Basically, the Dimension field is being sorted by the DimensionNo field and then, since the third rank parameter is being used, the expression returns the Dimension for the nth sorted value.  In the report screenshot above, the dimensions Product Name and Product Number were selected.  When you sort these by DimensionNo, Product Name comes before Product Number since 3 comes before 4 and when you rank these by their sorted value, Product Name is first and Product Number is second.  This same idea was used in all the dimensions as you can see in the second dimension shown below.

field2.png

The measures are done in the same manner.  Below is the Field and Label for the first measure and the additional measure columns will follow the same concept as the dimension columns.

 

Field

m_field.png

Label

m_label.png

Now the last step is to add a conditional show to the columns.  Basically, you only want the column to show if there is a dimension and/or metric selected to be displayed.  For the first dimension column, you will set the “Show column if” property to:

condition.png

For the second dimension, it will be the same except if greater than or equal to 2 and so on.  The first measure will have the “Show column if” property set to:

m_condition.png

Additional measure columns will follow the same format as described above for dimension columns.

 

Once all the columns are added and the properties are applied, the user can select the dimensions and metrics they would like to see in the report.  By default, the column order will be based on the DimensionNo field but feel free to drag columns in the table to a new position.  For more information on how Jason used FirstSortedValue, check out his document or Qlik Sense Help.

 

Thanks,

Jennell

20 Comments
cluscombe
Contributor III
Contributor III

Thanks Jennell! Really liking this. 

Is it possible to use this functionality with drilldown dimensions?  For example, could you make a drill down dimension for Month and Day instead of having Month and Day as separate dimensions?

0 Likes
17,268 Views
robert99
Specialist III
Specialist III

Here's an alternative way. (When someone is in a hurry. As I often am )

Users have been very happy with this option

Step 1. Step up list in script as follows

Step 2. Set up a filter pane and table (Pivot table) in a sheet.

Step three. Add columns > dimension or measures to table or pivot table

Step 4. Link the dimension or measures to the filter pane (the column will be hidden until the field is selected)

IF(WildMatch(GetFieldSelections(xReportType),'*CustName*'),1,0)

17,268 Views
robert99
Specialist III
Specialist III

Hi Chris

Unsure with the above approach. But my alternative you can do this.

0 Likes
17,268 Views
jerifortune
Creator III
Creator III

Thanks. This will add value to adhoc reports.

0 Likes
17,268 Views
michael_nordstr
Partner - Creator
Partner - Creator

Here is a new video on the Custom Report extension that jmc mentions above:

The Climber Custom Report 2.4 for Qlik Sense | Michael Nordström - YouTube

The extension is now also accredited by Qlik in the Trusted Extension Developer Program.

The extension is built specifically for this purpose and gives a lot more features as well as a much easier development and cheaper maintenance.

17,268 Views
Tyler_Waterfall
Employee
Employee

Much cleaner Conditional show than I had come up with! Thanks!

I can replace

=len('$(=Subfield(Concat(distinct [DIY Time Field],'|'),'|',1))')>1

with

GetSelectedCount([DIY Time Field])>=1

0 Likes
17,268 Views
mfarsln
Creator II
Creator II

Hello!

Is there a way to use formulas with set analysis into the 'MetricFormula' field? When i tried to do so, it gives me an error.

Capture.PNG

 

 

0 Likes
12,001 Views
JordyWegman
Partner - Master
Partner - Master

Hi MfarsIn,

I think this is only a text error, but Qlik will resolve this when you evaluate the expression.

Btw, you don't need the "" because you have an integer.

Jordy

Climber

0 Likes
11,996 Views
mfarsln
Creator II
Creator II

Hi Jordy,

Tried to load the data but it gave me an error;

Capture.PNG

 

Capture2.PNG

 

Edit: Problem was the brackets around the Values field. Thanks for your time Jordy.

0 Likes
11,992 Views
JordyWegman
Partner - Master
Partner - Master

Hi,

What if you add a replace? 

"Replace(Count({$< [Value] ^ {1}>}distinct CallID),'^', '=')"

Jordy

Climber

11,987 Views