Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Lode
Contributor
Contributor

How to treat missing values as 0 in pivot calculations

Hi, I'm new to Qlik Sense and I'm having difficulties with something that seems to be so simple if you're used Excel but doesn't seem to be working in Qlik.

I'm making a pivot table with sales of products per year. In a second column I put the difference between this year and previous year. The problem is, when I haven't had sales in a specific year, the formula doesn't find a record and puts a - (missing value) in the pivot table.

Of course if I haven't had a sale on a specific product this year, but i had last year, I should see a drop in my second column, but because Qlik doesn't make the calculation with the missing value field it again shows an -.

Is there a way to let Qlik Sense threat these missing values as 0?

See the example below. The 2 not so happy smiley's should show a difference in sales with last year of -100, but show missing value instead. Any way to solve this?

 2017 sales2018 sales2019 salesdelta 2017delta 2018delta 2019
Product 1100200100100(200-100) = 100(100 - 200) = -100
Product 2100-100100-    😑100
Product 310050100100-5050
Product 4100100-1000-      😑
Product 510010010010000

 

Thanks in advance

KR

Lode

Labels (3)
6 Replies
shiveshsingh
Master
Master

May be you can try this?

If ( Sum(Sales)='',0,Sum(Sales)) as Expression.

 

Try this or share the input file to explore it more.

Lode
Contributor
Contributor
Author

Hi Shiveshsingh,

thanks for you quick reply, but unfortunately this doesn't work.

I think it's because the sum formula finds no result for that combination, it generates no return and the rest of the formula is not executed.

Lode

shiveshsingh
Master
Master

Hi

 

Can you share some sample file?

Thanks

Lode
Contributor
Contributor
Author

Enclosed excel data file and my result in Qlik

 

Thanks in advance for you time

Lode
Contributor
Contributor
Author

excel data

shiveshsingh
Master
Master

Apologies!

I missed that you are using sense.

If the values are missing then expression will not be able to handle that part in Pivot table in qliksense, AFAIK!

You can use Straight Table instead.

You can refer this too 

https://community.qlik.com/t5/New-to-Qlik-Sense/Missing-values-in-PivotTable/td-p/940314