Replacing Null Values with Zeros in Qlik Sense Crosstable

[Mhena]

Hello everyone,

I’m encountering an issue with Qlik Sense where I created a crosstable directly in a sheet (without using the load script). When certain combinations of dimensions don’t have associated data, the cells remain empty. I would like to replace these null values with zeros to maintain consistency in my analysis.

Has anyone encountered this before or can suggest a solution? Any help would be greatly appreciated!

[diegozecchini]

Hi!
In this case what I would do is modify the measure using an If condition
First select the measure you are using, then modify it like this

If(IsNull(Sum(YourMeasure)), 0, Sum(YourMeasure))

[hic]

Whether this is possible or not depends on your data...

If the record Month='Apr', Region='South', Product='A' exists in your data, but is Null, then Diegos expression will work. Or better:
Coalesce( Sum(YourMeasure), 0 )

But if the combination doesn't exist, it will not work. Instead, you would need to create all combinations in the script, padding with zero where appropriate.

[Mhena]

Yes, exactly. I have some missing data for the combinations. Is there any other solution, aside from this one, as the real data I work with involves a large volume, which could make this approach difficult?

[Mhena · Accepted solution]

I tried this before, but the real problem is that some combinations of data are missing.

[BrandonFontes]

Usar o Rangesum pode ser uma saída. Mas em diversas situações que tive parecidas, eu precisei fazer o que foi comentado acima, precisei criar no meu Script os dados faltantes. Ou seja, preenchi para todas as informações com valores 0.

[marcus_sommer]

I suggest to rethink the impact of your aim. These NULL isn't wrong or even an error else logically correct. Replacing the NULL with anything else may lead to more confusion and misinterpretation as showing the data-set like it are.

[diegozecchini]

Hi!
if you want all possible combinations of Month, Region, and Product exist, you need to create a data model that generates these combinations. You can do this by script.

TempData:
LOAD Distinct Month
Resident YourData;

Join
LOAD Distinct Region
Resident YourData;

Join
LOAD Distinct Product
Resident YourData;

Outer Join (TempData)
LOAD Month, Region, Product
Resident YourData;

Then, when loading the actual dataset:

FinalData:
LOAD
Month,
Region,
Product,
If(IsNull(Sum(YourMeasure)), 0, Sum(YourMeasure)) as YourMeasure
Resident YourData;