Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ziabobaz
Contributor II

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
roharoha
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
b_knuever
Contributor III

Re: show "nothing" as Zeros in Pivot table

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

OmarBenSalem
Esteemed Contributor

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 II

Re: show "nothing" as Zeros in Pivot table

Tried this
does not work

ziabobaz
Contributor II

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

b_knuever
Contributor III

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.

roharoha
Valued Contributor III

Re: show "nothing" as Zeros in Pivot table

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

isingh30
Contributor III

Re: show "nothing" as Zeros in Pivot table

Please share your sample application or data?

Thanks

ziabobaz
Contributor II

Re: show "nothing" as Zeros in Pivot table

no )
Sense is sooooo limited on this

ziabobaz
Contributor II

Re: show "nothing" as Zeros in Pivot table

!

Community Browser