count if without nulls

[Facundo]

Hi, I'm trying to do a simple kpi but it's not working as intended.

This is retrieving all the distinct Prest, including the ones with sum(frecuency)/sum(counter)<=1

IF( SUM(Frequency) / SUM(Counter) > 1, count(distinct Prest))

In the table I have 155 values with sum(frecuency) / sum(counter)>1 (column C) and that's what the above expression should retrieve but is retrieving 162 and that's all, including nulls

Any ideas? or another approach?

Table:

[NitinK7 · Accepted solution]

Hi,

try like below

Count( Distinct IF( (Frequency / Counter)> 1,  Prest) )

[Gabriel]

Hi,

Try the example below.

//load your data here but adjust to your fieldname

tmp_NullCount:
LOAD *,
IF(ISNULL(ValueNullTransformed), 'T', 'F')                                        AS IsItNull;

LOAD *,
IF(LEN(TRIM(Value))= 0 OR Value='-', NULL(), Value )                 AS ValueNullTransformed;

LOAD * INLINE
[ID, Value
0,1.02
1,-
2,-
3,1.60
4,1
5,2
6,
7,-
8,12.9
];

Then in your UI, use below expression

=SUM({<IsItNull ={'F'}>} Value)

[Facundo]

This works, thanks!