topic Re: Count Blank and show in KPI in App Development

Count Blank and show in KPI

vpanchuda — Sat, 16 Nov 2024 05:52:55 GMT

I have a table with Dimensions as below and I Need to count blank in Action Plan Dimension and show it in a KPI, Can someone help me with Syntax, I am new to QLik Sense.

Please note it's blank it's NOT (---)

Target date	Responsible Rep	Action Plan
01-12-2019		Renew
02-12-2019	AJ	Swap
03-12-2019	RM
04-12-2019	AJ
05-12-2019
06-12-2019	VS	Renew
07-12-2019		Close
08-12-2019	VS	Close

Re: Count Blank and show in KPI

tresB — Fri, 10 May 2019 07:56:44 GMT

Try like:
=Count(if(len(trim([Action Plan]))=0,[Action Plan])

Re: Count Blank and show in KPI

Mauritz_SA — Fri, 10 May 2019 12:04:52 GMT

Hi there

I am not sure whether counting the [Action Plan] field will yield any results as you cannot count Null/Blank values. What you could try doing is counting the Target Dates where the Action Plan is Null (Length without spaces is 0). Try this: Count({<[Target date] = {"=Len(Trim([Action Plan]))=0"}>} [Target date])

If you want to use @tresB 's formula you can use:

=Count(if(len(trim([Action Plan]))=0,[Target date]))

Regards,

Mauritz

Re: Count Blank and show in KPI

tresB — Sat, 11 May 2019 07:40:06 GMT

@Mauritz_SA wrote:
Hi there
.....you cannot count Null/Blank values...
Mauritz

Actually you can count real NULLs, using NullCount()

Re: Count Blank and show in KPI

formosasol — Sat, 11 May 2019 11:23:40 GMT

NullCount - script function

NullCount() returns the number of NULL values aggregated in the expression, as defined by a group by clause.

Syntax: NullCount ( [ distinct ] expr)

Re: Count Blank and show in KPI

Mauritz_SA — Mon, 13 May 2019 09:48:10 GMT

Hi @tresB

That's true, I was referring to the Count function - I guess capitalisation on my side would have made that clear.

@vpanchuda I just checked and you can use NullCount([Action Plan]) as a measure if they are real Nulls.

Regards,

Mauritz

Re: Count Blank and show in KPI

vpanchuda — Mon, 13 May 2019 09:52:27 GMT

It shows '0' as result , actually the cells are not filled (if the cells are left empty in the table are they real Nulls or Blank values??) i assume its blank values , if its blank is there any possible way to count it?

Re: Count Blank and show in KPI

Mauritz_SA — Mon, 13 May 2019 10:11:20 GMT

The way I understand it, if there are blank values in the source table (or after a join) then they are real Nulls. I think it would be best to share a sample app 🙂