Replacing 0 for '-' Qlik sense

[UserID2626]

Hi,

I want to to replace '-' as Zero. 

Kindly find the below image for reference.

In my source not having data for Product C & D for the month 2019-oct, i want to replace '-' as zero.

Thanks!!!

[JordyWegman]

Hi Nivetha,

Because there are no values, Qlik is returning null(), but Qlik Sense doesn't offer a replace option like in QlikView.

You can use the following:

IF(isnull(Sum(result)),0,Sum(result))

Jordy

Climber 

[UserID2626]

Thanks  Jordywegman,

In set expression, Is there is option to find that for Product C & Oct-2019 not having data in source.

[JordyWegman]

What do you mean exactly? You can replace your measure with the one I have proposed and use your metrics/fields here. 

You can find in your data model viewer that you will have a subset ratio that is not 100% because some combinations are missing.

Jordy

Climber

[JordyWegman · Accepted solution]

Did you remove a last post? I'm replying to that one.

If this really is a big issue then your problem should be solved a bit differently and in the data load editor.

1. Create all year month combinations
2. Left join your fact table to the year month combinations
3. For the field with your values, applymap these values and if there is no value, return a 0.

What you have now are values for all year month combination, also if you originally didn't had any. This will be filled with 0's. You will now be able to make this good MOM.

Yes it can be solved otherwise with a lot of IF statements, but these will make your report slow.

Jordy

Climber

[UserID2626]

Hi Jordy,

Kindly help me with if condition.

[JordyWegman]

Hi,

There is not really an if statement, because we are using the functionalities of the applymap. 

ApplyMap('YourApplyMapYouMade,[FieldUsedForLookUp],0)

The 0 is what you return if the statement can't find your [FieldUsedForLookUp].

Jordy

Climber