Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

L_Hop
Contributor

How to show zero values in pivot table

Hello everyone,

 

I have a pivot table;

producer and product as a row, scenarios on column which can be selectable from the left top of the selections and as a measure I have quantity and price values.

 

When  I have select a producer from the producer filter which has no values for  selected scenario, I am only able to see scenairos which has the data have, for the below picture I am not able to see 'AC 2018' values for the selected 'Producer'.

 

What I want is even if there is no value for the selected scenario, I want to see zero values for the selected scenario.

 

For example; for the below picture 'AC18' , 'OP18'  and 'AC17' selected from the scenarios and I have also selected a producer which has no data for scenario 'AC18'. I am only able to see 'AC 2017' and 'OP 2018' in pivot table but I have also want to see 'AC 2018' column as zero. How can I solve this problem?

Please suggest me any solution if anyone of you gone through the same problem. Any help will be appreciated.

Thanks in advance.

OY

4 Replies
pascos88
Contributor II

Re: How to show zero values in pivot table

Hi

maybe you can try to write an expression:

if (isnull(field), 0, field)

L_Hop
Contributor

Re: How to show zero values in pivot table

Hi,

I have tried but not working.

Thanks.

OY.

Highlighted
claude_jakob
New Contributor III

Re: How to show zero values in pivot table

Qlikview normally suppresses 0 (zero) values. Adding a minimal value (e.g. 0.0001) to your measure either in your expression or upon data load should do the trick:

= Sum(measure) + Sum(TotalMeasure) / Sum(TotalMeasure)*0.0001

pascos88
Contributor II

Re: How to show zero values in pivot table

So than you could use a table, and use x measure for the x field that you want show (AC18' , 'OP18'  and 'AC17'), using a set expression in the script : max({<field = {'AC18'}>})

Otherwise I think that you have to change a bit your dm and generate rows even if you do not have values insiede.