topic Re: Replacing Null Values with Zeros in Qlik Sense Crosstable in App Development

Replacing Null Values with Zeros in Qlik Sense Crosstable

Mhena — Mon, 17 Feb 2025 08:47:00 GMT

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!

Re: Replacing Null Values with Zeros in Qlik Sense Crosstable

diegozecchini — Mon, 17 Feb 2025 09:38:16 GMT

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))

Re: Replacing Null Values with Zeros in Qlik Sense Crosstable

hic — Mon, 17 Feb 2025 09:57:52 GMT

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.

Re: Replacing Null Values with Zeros in Qlik Sense Crosstable

Mhena — Mon, 17 Feb 2025 10:45:01 GMT

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?

Re: Replacing Null Values with Zeros in Qlik Sense Crosstable

Mhena — Mon, 17 Feb 2025 10:46:00 GMT

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

Re: Replacing Null Values with Zeros in Qlik Sense Crosstable

BrandonFontes — Mon, 17 Feb 2025 13:17:31 GMT

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.

Re: Replacing Null Values with Zeros in Qlik Sense Crosstable

marcus_sommer — Mon, 17 Feb 2025 13:58:03 GMT

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.

Re: Replacing Null Values with Zeros in Qlik Sense Crosstable

diegozecchini — Mon, 17 Feb 2025 14:04:22 GMT

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;