Remove the entire row when measure returns 0

[Keerthi1234]

Hi All,

I need to remove the entire row when something returns null. While working with dimension, unchecking the include null values works perfectly, The similar action I need to perform in a measure.

This is the final table that I am seeing now

APM NUMBER	PRODUCT	Fiscal year period	Total cost	Product Specific sum
123	Apple	2020001	3	$3
123	Apple	2020002	0	-
123	Apple	2020003	3	$6
456	Apple	2020001	4	$10
456	Apple	2020002	5	$15
456	Apple	2020003	6	$21
678	Apple	2020001	1	$27
678	Apple	2020002	0	-
678	Apple	2020003	3	$30
910	Banana	2020001	1	$1
910	Banana	2020002	2	$3

Here the total cost column is a sum(field 1 +field2) so it always returns 0 if there is sum(null,0), While calculating cumulative cost the calculations are performed rightly but this issue happens. 

Now I want to remove the entire row that returns - ,how to do that?

I tried the combination of if and not isnull but those return the same table.

Has anyone faced this issue? Please help me resolving this

Regards,

Keer

[Ben_P · Accepted solution]

Add-ons > Data handling > untick Include zero values should do it. At least in Qlik Cloud.

[SRA]

Hi,

In the "Add-ons / Data handling" parameters of your Table, if you check "Include zero values" (checked by default) then you will see the lines with 0.

But if you uncheck this parameter, 0 lines will desapear :

Does this answer your question?

Regards,

SRA

[sbaro_bd]

Hi @Keerthi1234 ,

https://community.qlik.com/t5/New-to-Qlik-Analytics/Cumulative-Sum-0-value-evaluates-instead-of-valu...

What's about this solution? It works or not!?

Regards.

[Chanty4u]

Try this 

Sum([Total cost]) <> 0

Or 

IF([Total cost] <> 0, [Total cost])

Or

IF([Total cost] <> 0, RangeSum(Above(Sum([Field1] + [Field2]), 0, RowNo())))

[Keerthi1234 · Accepted solution]

Thank you @Ben_P ! This works 🙂

[Keerthi1234]

Thanks for your response guys! Unchecking Include zero values works fine!

[sbaro_bd]

Hi @Keerthi1234 ,

Can you please mark @Ben_P response as a solution? Thank you.

Regards.

[diegozecchini]

smart solution, thanks

[Keerthi1234]

Hi @sbaro_bd ,

Sorry I missed replying , No this does not work in my case.

Thanks for the response.

Thanks,

Keer