Announcements
cancel
Showing results for
Did you mean:
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

Labels (3)

• zero

6 Replies
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.

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

Master

Hi

Can you share some sample file?

Thanks

Contributor
Author

Enclosed excel data file and my result in Qlik

Thanks in advance for you time

Contributor
Author

excel data

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