Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
ziabobaz
Contributor III

show "nothing" as Zeros in Pivot table

Hi!

I am totally confused.

Is there way to show missing values as Zero ('0')?

I know that these values are non-existent in this fields, but I am sure there should be a way of showin it as zeros.

Screenshot_5.jpg

I am using mastercalendar, if it matters.

This is my datamodel:

Screenshot_6.jpg

Tags (4)
1 Solution

Accepted Solutions
robin_hausdoerfer
Valued Contributor III

Re: show "nothing" as Zeros in Pivot table

I prepared a demo for you...

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='YYYY-MM-DD';

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='de-DE';

SET CreateSearchIndexOnReload=1;

for i= MakeDate(2018,1,1) to MakeDate(2018,3,31)

    Master:

    LOAD Date($(i)) as Datefield AutoGenerate 1;

next

Facts:

LOAD *,Datefield&'|'&Operation as TEMPKEY;

LOAD *INLINE [

Datefield, Operation, Amount

2018-01-15, Sales, 500

2018-02-15, Sales, 1000

2018-03-15, Purchases, -400

];

//fill up Sales

concatenate(Facts)

LOAD

Datefield, 0 as Amount, 'Sales' as Operation  resident Master

where not Exists(TEMPKEY,Datefield&'|Sales');

//fill up Purchases

concatenate(Facts)

LOAD

Datefield, 0 as Amount, 'Purchases' as Operation  resident Master

where not Exists(TEMPKEY,Datefield&'|Purchases');

drop field TEMPKEY;

15 Replies
Partner
Partner

Re: show "nothing" as Zeros in Pivot table

if (len(value)>0, value, 0)

Partner
Partner

Re: show "nothing" as Zeros in Pivot table

can u please add in the script:

NULLASVALUE *;

SET NullValue ='0' ;

reload and see what happens?

ziabobaz
Contributor III

Re: show "nothing" as Zeros in Pivot table

Tried this
does not work

ziabobaz
Contributor III

Re: show "nothing" as Zeros in Pivot table

does not work both in the sript and in the formula

I think it won't solve the problem

because I have data missing on weekends, and therefore, it just does not record anything on weekends, so there is nothing to check for length

Partner
Partner

Re: show "nothing" as Zeros in Pivot table

You need to put it in the chart and value is the formula or what ever you calculate. This should work.

robin_hausdoerfer
Valued Contributor III

Re: show "nothing" as Zeros in Pivot table

Isn't there any option as there is one in QlikView?!?

isingh30
Valued Contributor

Re: show "nothing" as Zeros in Pivot table

Please share your sample application or data?

Thanks

ziabobaz
Contributor III

Re: show "nothing" as Zeros in Pivot table

no )
Sense is sooooo limited on this

ziabobaz
Contributor III

Re: show "nothing" as Zeros in Pivot table

!