Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ziabobaz
Creator III
Creator 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

1 Solution

Accepted Solutions
Anonymous
Not applicable

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;

View solution in original post

15 Replies
bramkn
Partner
Partner

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

OmarBenSalem
Partner
Partner

can u please add in the script:

NULLASVALUE *;

SET NullValue ='0' ;

reload and see what happens?

ziabobaz
Creator III
Creator III
Author

Tried this
does not work

ziabobaz
Creator III
Creator III
Author

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

bramkn
Partner
Partner

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

Anonymous
Not applicable

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

isingh30
Specialist
Specialist

Please share your sample application or data?

Thanks

ziabobaz
Creator III
Creator III
Author

no )
Sense is sooooo limited on this

ziabobaz
Creator III
Creator III
Author

!