Avoid Dimension Value going to ZERO (0) Bucket

[#1]

In above picture, it is a pivot table. In properties, Suppress Null Value is Unchecked because the requirement is to show Product Type and SL_Product_Name even when there is no Dollar value linked to them.

But the issue is, if there is not dollar amount for funnel, revenue and sales - the SL_PRODUCT_NAME goes to '0' bucket as you can see above.

If i select Suppress Null Value, the '0' will go away but so will the product names that doesn't have any zero dollar.

In cases when there is no amount in all three columns, even Hosting goes to 0(zero) bucket.

How can i still place SL_PRODUCT_NAME in their respective buckets even when they don't have any dollar value linked to them?

I appreciate any help.

Thank you,

[sunny_talwar]

Can you add a snapshot of what happens when you do suppress null so that we can do a comparison of what is actually happening.

Best,

Sunny

[#3]

Sunny, this what it looks like when i check the Suppress Null Value box. zero bucket disappears. I need the product names to still appear in their respective bucket/product type and display value(in funnel, revenue and sales) as zero.

[sunny_talwar]

So essentially you want to get rid of the final row from your first screenshot and just see the 2nd screenshot +  this below?

[#5]

Thank you for your prompt response, Sunny.

Yes, but those rows should go to proper Product Type and not to 0(zero). 0(zero) is being created as a separate bucket (under Product Type column heading) whereas there should have been only Hosting and Non-Hosting as two buckets. It seems like a default feature of Qlik, i am trying to find workarounds for it.

[sunny_talwar]

Well that seems more like a data issue. There seems to be some rows in your database which has SL_PRODUCT_NAME but not PRODUCT_TYPE. If you fix that, those Product Name which got assigned to 0 Product type should automatically get fixed.

To check this create a table box with SL_PRODUCT_NAME and PRODUCT_TYPE and if possible share that here.

Best,

Sunny

[#7]

Sunny,

That could be the reason, i will check and post it here.

Thank you again,

Prachyut

[sunny_talwar]

Yup Not a problem. I hope you find the bug quickly.

Best,

Sunny

[#9]

Sunny,

A quick question, does qlik allocates a null bucket/row besides values of a field in database?

I see in database, there is a product type to each product name. but for some reason, when we uncheck the check box for Suppress Null Values, it just creates an empty row and a empty column as well.

Thank you,

Prachyut

[#10]

There is a row and a column for null even when i use following expression.

=Match(PRODUCT_TYPE,'Hosting', 'Non-Hosting')