Sum(quantity) when province is null

[sersal10]

Hi all,

I have 2 fields province and quantity, and need to find how much is the total quantity of the null provinces.

I am trying something like this but doesn´t work:

Sum({<province = {NULL}>} quantity)

or

Sum({<province={"=Len(province) = 0"}>} quantity)

Kind regards,

Sergio.

[Clement15]

Hello, can you elaborate? For me your second formula works well

[sersal10]

Hello Clement,

Yes, in the dataset that I load via data editor I have 2 tables. one contains city, province,country, and the other one id, name, quantity, city, country. and in this table are nulls as there are empty values. If I create a KPI with CountNulls(country) it displays 700. but can´t find the way to sum their quantities.

Also, CountNulls(province) delivers 0 which should be 700 too.

[Clement15]

Are you sure that province is null and that there is no space in it? You can use the Trim() function to check.

Sum({<province ={"=Len(trim(province)) = 0"}>} quantity)

[sersal10]

I edited my question as the name of the field is province instead of country. 

It seems the problem is with the data though.

[sersal10]

This is a capture of the data

[sersal10]

And this NullCount(province) results in 0. It should be 1700.

[Clement15]

And could this work?
Sum({<quantity ={"=Len(province) = 0"}>} quantity)

[sersal10]

Very weird. It gives me the total now. 5000.

[Clement15]

N’est-ce pas là la bonne somme de quantités ? Si vous utilisez cette formule, vous n’avez pas 700 ?
Count({<quantity ={"=Len(province) = 0"}>} quantity)