Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

count if without nulls

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:

Xijamk_0-1595276437069.png

 

 

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Hi,

try like below

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

View solution in original post

3 Replies
Highlighted
Creator III
Creator III

Hi,

try like below

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

View solution in original post

Highlighted
Partner
Partner

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)

 

Highlighted
Contributor
Contributor

This works, thanks!