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

[prads_uk_2025]

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? 

[hugo_andrade]

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?

[Lech_Miszkiewicz]

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

[prads_uk_2025]

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.

[Lech_Miszkiewicz]

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  

[prads_uk_2025]

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.

[Lech_Miszkiewicz]

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:

1. Add levels
2. Go to sheet section where your tables are and add the 2 tables which will be used as levels
3. add level table for most outer dimension (with 1 dim)
4. add level for outer and inner dimension (with 2 dims)
5. Make sure you dont use "Fields" section as that what is causing your issue currently
6. 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

[prads_uk_2025]

Portfolio Summary

<Fund Name_1><vMaxDate_1>

<Facility Class Type for HYSL Report_2_Level>

<Facility Class Type for HYSL Report_2>

<Facility Class Category for HYSL Report_3_Level>

<Facility Class Category for HYSL Report_3>

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>

<deleterow>

0.00 0.00 0.0%

<deleterow>

</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.

[Lech_Miszkiewicz]

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-l...  

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

cheers

Lech

[prads_uk_2025]

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