Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vvvvvvvvvv
Creator
Creator

Use zero instead of null in pivot table

Is there any way to replace '-' by 0 in QS pivot table?

I have a measure to count the number of record by using count(1). But the zero count will be shown as '-' instead of 0 .

How to display 0 for these case?

Labels (6)
10 Replies
G3S
Creator III
Creator III

Could you try 

if(yourExpression=0,'0',yourExpression)

vvvvvvvvvv
Creator
Creator
Author

no. already tried still not work.

count(1) will become '-' when there is no record

Kushal_Chawda

@vvvvvvvvvv  If the null value is generated due to missing data then you can't replace it. The only way then is to generate missing data in your table . Look at below link

https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394

 

Steven_Haught
Creator III
Creator III

@vvvvvvvvvv  

As Kush mentioned above you will have to create the 0 Values. You can try using If(Isnull(YourField),0, YourField) It may be best to do in the load as well. 

vvvvvvvvvv
Creator
Creator
Author

if my table is like below

A  B  C
-------------
A1 B1 

A1 B1 

A2 B2 

the pivot table would be like this (counting number of records)
        A1     A2

B1   2         -
B2   -          1

But I want - to be 0 
how to generate the missing data???

Steven_Haught
Creator III
Creator III

@vvvvvvvvvv 

Try using this in the measure that you are doing the count: 

If(IsNull(YOURMEASURE),0,YOURMEASURE)

vvvvvvvvvv
Creator
Creator
Author

not work

Steven_Haught
Creator III
Creator III

Can you share some more info on the data and how your measure? 

madajank
Contributor
Contributor

Pivot table:

madajank_0-1693401101155.png


How to remove the NULLs here?
- IsNull(Measure) or Measure = 0 and make the color background & text as white doesn't work
- NUM(Measure,'#;-#;') shows nulls as '-'

We're now in 2023 - Qlik Sense May 2023 release