topic Re: How to limit TOP X by a measure not used in a Chart in Qlik NPrinting

How to limit TOP X by a measure not used in a Chart

Nils_Schindler — Tue, 01 Mar 2022 16:15:58 GMT

Hi everyone,

is there a way to limit a chart to the Top X (e.g. Top 10) values of a measure, that is not used in that chart?

I have tried this solution, which works perfect in Qlik Sense:

https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-limit-top-10-by-a-measure-not-used-in-chart/td-p/33373

But when I try to use that "filtered" chart in Nprinting, it is just empty.

I think the problem is the same as with if-clauses in tables, where the designer cannot find them anymore after "filtering" a table using an if-clause.

Attached you can find a screenshot showing, what the diagram looks like with the values missing.

Thank you in advance for your help!

Re: How to limit TOP X by a measure not used in a Chart

Ruggero_Piccoli — Wed, 02 Mar 2022 14:13:03 GMT

Hi,

I think the problem is that calculated dimensions are not supported https://help.qlik.com/en-US/nprinting/May2021/Content/NPrinting/ReportsDevelopment/Qlik-objects-supported-NP.htm#anchor-3

I haven't a similar example ready right now for Excel. Maybe you could try to export all data in a hidden Excel worksheet and use Excel features to extract the top10 for the viewer.

Best Regards,

Ruggero

Re: How to limit TOP X by a measure not used in a Chart

Nils_Schindler — Wed, 02 Mar 2022 14:32:49 GMT

Hi Ruggero,

thank you for the hint. Probably you are right, I will just try to filter in Excel.

Kind regards,

Nils

Re: How to limit TOP X by a measure not used in a Chart

Ruggero_Piccoli — Wed, 02 Mar 2022 14:41:11 GMT

Hi,

When you have a solution, please, evaluate to share it here. I will also try to find it.

Best Regards,

Ruggero

Re: How to limit TOP X by a measure not used in a Chart

Lech_Miszkiewicz — Thu, 03 Mar 2022 04:08:40 GMT

Hi,

this needs more description.

There are certain ways we can approach this, but we need to know:

where such object will be used (Level or Table or image or something else)
are there any other objects on that report
what is the template you are trying to use that object in - it seems to be excel, so you could use object filter and "Advanced Search" to filter data in that object using whatever measure you need
- that again requires thinking about dimensions used in that object.

Also it is hard to say if you need to use it in Level or in Table.

Another thing is that having calculated dimension in the Qlik Sense table which is used as a table in NPrinting is still supported and works so I am not sure what you were trying to say by: "I think the problem is the same as with if-clauses in tables, where the designer cannot find them anymore after "filtering" a table using an if-clause." I am guessing you were trying to hide a column which will not be supported.

Lastly - what is stopping you from adding the required measure to the chart? That particular chart does not need to be exposed to users in Qlik Sense app. It can be in hidden sheet and can simply be used as NPrinting only object.

As always - i suggest to step back, think about what you want to achieve, try thinking outside the box and implement the most appropriate solution.

So yeah - there are at least 3 solutions i can see working easily depending on scenario you are in.

cheers

Lech

Re: How to limit TOP X by a measure not used in a Chart

Nils_Schindler — Thu, 03 Mar 2022 08:54:35 GMT

Hi Lech,

thank you for your answer, I will try to give you a better description of my problem:

The report I am trying to create is an Excel Report containing project data.

There will be a table with all active projects on the first sheet, a table with only the TOP 40 projects for one calculated Measure (biggest 40) in the second sheet. These both work fine, the TOP 40 I could get with the limitation - fixed number function in Qlik Sense.

Then there will be 3 sheets with images that visualize certain measures as bar charts, but only the projects within the top and bottom 10 for the visualized measures out of these 40 biggest projects.

Since the measure defining these 40 biggest projects is not included in the visualizations and also I need top 10 and bottom 10, I cannot use the limitation - fixed number option.

So I decided to use a calculated dimension and the option to not include NULL-values in for the Projectnames in the visualization with the following formula:

=aggr(if(rank([Measure for biggest 40])<=40,aggr(if((rank([Measure for TOP and bottom 10])<=10 or rank(-[Measure for TOP and bottom 10])<=10),[Projectname]), [Projectname])),[Projectname])

This version works fine in Qlik Sense but gives me empty images in NPrinting (probably because of the calculated dimension) I have tried to use this formula as a filter, but that does not work aswell.

I have also tried to calculate the measures in the loadscript already, to have a fixed ranking. This also did not work, since the data structure comes very complex from SAP. But even if this worked well, it would not be a good solution, since the report will be filtered user specific and only contain the values for e.g. a certain business unit and so I needed different ranking columns for each of them.

Kind regards,

Nils

Re: How to limit TOP X by a measure not used in a Chart

Lech_Miszkiewicz — Thu, 03 Mar 2022 09:07:26 GMT

Ok it is all clear now.

You are facing issue describe in this topic as instead of writing calculated dimension using fully expanded expressions you used measure names which is causing issues:

This is a topic:

https://community.qlik.com/t5/Qlik-NPrinting-Discussions/Gauge-from-Qlik-sense-into-Nprinting/td-p/1839212

and this is suggested Qlik solution:

https://community.qlik.com/t5/Knowledge/Qlik-Sense-charts-with-master-measures-used-in-Qlik-NPrinting/ta-p/1882332

cheers

Lech

by the way - as you can see providing detailed info straight away gives us indication what is wrong and allows us to provide you with constructive answer !!!

Re: How to limit TOP X by a measure not used in a Chart

Nils_Schindler — Thu, 03 Mar 2022 10:49:48 GMT

Thank you Lech!

Now it works as expected!