Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
fmazzarelli
Contributor III

How to exclude null values from a sum?

Hi Community,

How to exclude null values from a sum?Immagine.png

Totale Ore fatturabili = 20.743,95 but I need to exclude (-) values.

The correct value will be 20.743,95 - 4.097,98

Could you suggest a method?

Thanks in advance

5 Replies

Re: How to exclude null values from a sum?

May be like this:

Sum({<DimensionName = {'*'}>}Measure)

nagarjuna_kotha
Valued Contributor II

Re: How to exclude null values from a sum?

if is have null values in your field , you can remove those in two ways

Way 1(in Dimensions): In your chart , gooto dimension tab , select ur dim and enable supress null option,

Way 2 (By expression) : Sum ({<Dimension Name={'*'}>}Sales)

I hope this may helps you.

-Nagarjun

fmazzarelli
Contributor III

Re: How to exclude null values from a sum?

Hi,

thank for your reply.

My needs is:

i select *  from a database..... example 3 rows

worker, hour

1          10

2          7

3          5

then

using an external .xls file, I create personalized data... example 1 rows

worker name

1     Fabio

All data are joined by the field Workers

Using a pivot tabel, how could exclude rows not in join? 2 e 3

MVP
MVP

Re: How to exclude null values from a sum?

Sum({<name = {'*'}>} hour)

fmazzarelli
Contributor III

Re: How to exclude null values from a sum?

Hi,

I ASK A LITTLE PATIENCE, probably you're pointing to the solution but I don't understand

Column

TOT

1.508,75

1.484,75

.......

to calculate the values, I use this formula

Sum(DIRFIS)

+ sum({<Commessa = {"=KeepChar(Commessa, '0123456789') * 1 > 10"}>} [TotOreLavorate])

+ sum({<Commessa = {'ASSCLI', 'ASSREM'}>} [TotOreLavorate])

+ sum({<CTRAAN = {'N',""},Commessa = {'ASSTEL'}>} [TotOreLavorate])

+ sum({<CTRAAN = {'T',""},Commessa = {'ASSMOD'}>} [TotOreLavorate])

and I don't understand how to adapt your formula to my.