# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New 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 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

KR

Lode

Tags (1)
Labels (4)

• ### zero

6 Replies
Highlighted
Honored Contributor

## Re: How to treat missing values as 0 in pivot calculations

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.

Highlighted
New Contributor

## Re: How to treat missing values as 0 in pivot calculations

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

Highlighted
Honored Contributor

## Re: How to treat missing values as 0 in pivot calculations

Hi

Can you share some sample file?

Thanks

Highlighted
New Contributor

## Re: How to treat missing values as 0 in pivot calculations

Enclosed excel data file and my result in Qlik

Thanks in advance for you time

Highlighted
New Contributor

## Re: How to treat missing values as 0 in pivot calculations

excel data

Highlighted
Honored Contributor

## Re: How to treat missing values as 0 in pivot calculations

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