Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Partner
Partner

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

Partner
Partner

can u please add in the script:

NULLASVALUE *;

SET NullValue ='0' ;

reload and see what happens?

Creator III
Creator III

Tried this
does not work

Creator III
Creator III

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

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

Specialist
Specialist

Please share your sample application or data?

Thanks

Creator III
Creator III

no )
Sense is sooooo limited on this

Creator III
Creator III

!