topic Re: Remove or Do Not show the NULL values / rows / Levels in Reporting Service & Alerting

Qlik Excel reporting Remove or Do Not show the NULL values / rows / Levels

prads_uk_2025 — Thu, 04 Sep 2025 15:45:49 GMT

Hi,
I am working on Qlik Saas Excel Add-Ins reporting.
I have 2 Levels:
Asset Type: Long Book
Category: Equity, NA, PIK Note
as can see from the screenshot below:
Since, Equity and NA have no values #REF is coming whereas, it should not be in PDF at all.
How can I omit it?

Re: Remove or Do Not show the NULL values / rows / Levels

hugo_andrade — Wed, 03 Sep 2025 20:48:53 GMT

Hi @prads_uk_2025 ,

Is this behavior going to be frequent? Then I suggest using an IF() to check if the values are null and place a space instead.

Would Conditional Formatting help you here? Something like, set the text color to White if the function returns the #REF?

Re: Remove or Do Not show the NULL values / rows / Levels

Lech_Miszkiewicz — Thu, 04 Sep 2025 09:27:59 GMT

Hi @prads_uk_2025

I have to disagree with @hugo_andrade on this as managing levels in this case should be done by creation of straight table objects with dimensions you use in your levels and respective measures.

In your case it would be 2 extra tables you would need to create in Qlik Sense only for purpose of using them as levels instead of currently used fields.

one with Asset Type dimension and required measures
second with Asset Type and Category as dimension and required measures
on both dimensions in your additional tables you then disable show null and also disable include 0 values

Since particular data does not exist for the combionations you are having the #REF errors you can then build your levels using those table objects (and not using just fields) and since data in those tables will only produce values for what you need ref errors should not be even created. and you would only show data for those combinations of values where numbers are produced

cheers

Lech

Re: Remove or Do Not show the NULL values / rows / Levels

prads_uk_2025 — Thu, 04 Sep 2025 09:42:57 GMT

Yes, @Lech_Miszkiewicz following it.
Per you answer above, I have followed point no.2 and created separate tables for each of those categories. Have included both those fields in the Straight tables. Also, Null is removed from Dimensions as well as from Add-Ons.
Let me try your point no.1 and then create a template with that 1 and 2 combination. I have just thought of point no.2 and not 1.

Re: Remove or Do Not show the NULL values / rows / Levels

Lech_Miszkiewicz — Thu, 04 Sep 2025 11:45:03 GMT

You need to have both levels based on tabels as only then you will have required granularity to remove unwanted rows. There are no shortcuts there.

Cheers

Re: Remove or Do Not show the NULL values / rows / Levels

prads_uk_2025 — Thu, 04 Sep 2025 13:32:16 GMT

Hey Lech,
Still no change in the PDF.
Created 2 tables per your advise.
Added them in the Excel template per the level as well. I think I am adding the Level directly and so it is picking up all the values irrespective of the data in the Qlik Straight table.

Re: Remove or Do Not show the NULL values / rows / Levels

Lech_Miszkiewicz — Fri, 05 Sep 2025 00:36:31 GMT

Hi @prads_uk_2025

I am not there with you so I dont see what you do. You say: "I think I am adding the Level directly and so it is picking up all the values irrespective of the data in the Qlik Straight table." - Maybe share screenshots of steps you have taken.

in short you should be adding your 2 tables as levels as per below points:

Add levels
Go to sheet section where your tables are and add the 2 tables which will be used as levels
add level table for most outer dimension (with 1 dim)
add level for outer and inner dimension (with 2 dims)
Make sure you dont use "Fields" section as that what is causing your issue currently
add tables as levels and respective fields from it (if you need to) to your template. - thats it!

I tested it on my sample data and it worked as designed so it should work for you too. If it doesn't you may want to capture steps you take with screenshots so we can spot where the error is.

cheers

Re: Qlik Excel reporting Remove or Do Not show the NULL values / rows / Levels

prads_uk_2025 — Fri, 05 Sep 2025 09:08:20 GMT

Portfolio Summary

Issuer Asset Country Moodys Rating Maturity Coupon Spread Average Cost Current YTM

<Issuer_3> <Asset_3> <Country of Domicile_1> <Moodys Rating_2> <Maturity Date_1> <Coupon Spread_1> <Average Cost_1> <Average YTM_1>

0.00 0.00 0.0%

</Facility Class Category for HYSL Report_3_Level>

</Facility Class Type for HYSL Report_2_Level>

I am trying to do this. The one which you describe do not ahieve my goals.

the output of above should look like this:

whereas I am getting tables and records which does not exist even in Qlik Dashboard.

Tried implementing your suggestion as well:

Output:

Not the required output.

Re: Qlik Excel reporting Remove or Do Not show the NULL values / rows / Levels

Lech_Miszkiewicz — Fri, 05 Sep 2025 09:35:40 GMT

Hi @prads_uk_2025

I dont understand why your tables have so many dimensions? Why do you have dimensions like:

[Asset Name] or [Country of domicile] in tables used for levels if your granularity of data needs to be only on 2 other levels? If you create a table which has too many dimension report will be produced as for each row of such table.

I guess you may want to study how levels work from documentation. Focus on level from charts section: https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Reporting/add-in-excel-levels-pages.htm#anchor-1

Your tables used for your levels shouldn't have any of those dimensional fields:

cheers

Lech

Re: Qlik Excel reporting Remove or Do Not show the NULL values / rows / Levels

prads_uk_2025 — Fri, 05 Sep 2025 09:54:25 GMT

Employer's reqirement not mine. They have already achieved it using Power BI report builder and due to migration, they are expecting the similar reports from Qlik Cloud

Re: Qlik Excel reporting Remove or Do Not show the NULL values / rows / Levels

Lech_Miszkiewicz — Fri, 05 Sep 2025 22:38:09 GMT

Hi @prads_uk_2025

To achieve certain things you need to prepare your qlik app in certain ways. I explained what you need to do and that is to build tables with certain dimensionality as that drives behaviour of your report.

if you dont do that then there is no point me trying to explain how things work. Those steps were compulsory and there is no point trying different things like for example using tables with 5 dimensions for level which requires one dimension.

This is very basic report and building it must follow simple approach of

building UI table with ONE DIMENSION AND MEASURES for most outer level
building UI second table with TWO DIMENSIONS AND MEASURES for inner level
using third table with all dimension as source of rows with data for your report

thats it. It is as simple as that and you just have to do it because this is the method of doing things in this tool.

I don't care about PBI and how it works. It is a different tool, it works different. Qlik works in different way and requires approach i have provided you to achieve outcome you want using it.

Simple as that. Sorry but that is the reality.

cheers

Re: Qlik Excel reporting Remove or Do Not show the NULL values / rows / Levels

prads_uk_2025 — Tue, 09 Sep 2025 09:45:46 GMT

Achieved it using IFERROR(SUM(Cellvalues),0)