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
