How to treat missing values as 0 in pivot calculations

[Lode]

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 sales	2018 sales	2019 sales	delta 2017	delta 2018	delta 2019
Product 1	100	200	100	100	(200-100) = 100	(100 - 200) = -100
Product 2	100	-	100	100	-    😑	100
Product 3	100	50	100	100	-50	50
Product 4	100	100	-	100	0	-      😑
Product 5	100	100	100	100	0	0

Thanks in advance

KR

Lode

[shiveshsingh]

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]

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]

Hi

Can you share some sample file?

Thanks

[Lode]

Enclosed excel data file and my result in Qlik

Thanks in advance for you time

[Lode]

excel data

[shiveshsingh]

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