How to exclude the subtotal line when all values are 0?

[JR_38]

Hi everybody,

I've configured a straight table in a Qlik Cloud app.
I have activated the new "Subtotals" option for 3 dimensions.
For all dimensions, I have excluded null values.

All null values are no longer displayed, which is what I wanted, but subtotals with a value of 0 are still being listed.

Does anyone have an idea how to get these excluded as well in the straight table?

[Chanty4u · Accepted solution]

Hi

your measure is something like:

Sum(Sales)

use a calculated measure that returns NULL instead of 0:

If(Sum(Sales) <> 0, Sum(Sales))

or:

If(Sum(Sales) = 0, Null(), Sum(Sales))

Then keep Null values → Exclude enabled.

[SunilChauhan]

In Data load editor

in where Condition of Table mention like below

Where  FIeldName<>0  and Isnull(FIeldName)=0

Excel Data :

Data :
Load *
where Value<>0 and isnull(Value)=0;
LOAD
Test,
Value
FROM [lib://Test_Inv/01 Retail Inventory.xlsx]
(ooxml, embedded labels, table is Sheet1);

Hope this helps

[JR_38]

Hi CHanty4u,

thanks for your reply.

I have first of all created a variable in order to check if the sum is 0 or not.
vZeileLeer =
Sum(Do_akt)=0 and Sum(Do_VJ)=0 and Sum(Woche_akt)=0 and Sum(MTD_akt)=0
and Sum(MTD_VJ)=0 and Sum(YTD_akt)=0 and Sum(YTD_VJ)=0

Then, I have entered the variable in every single measure like following:
=If($(vZeileLeer), Null(), <current measure expression>)

I had to use the variable also in one dimension:

=Aggr(
If($(vZeileLeer), Null(), COLLECTION),
REGION, COLLECTION
)

It has worked at the end 🙂