Qlik Community

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 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

Tags (1)
Labels (4)
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