topic Re: how to count null records in QlikView

how to count null records

MarcoARaymundo — Wed, 09 Oct 2013 15:37:37 GMT

Hi!

Table A

YEAR	MONTH	PROVIDER
2013	08	P0004
2013	08	P0003
2013	08	P0002
2013	07	P0002
2013	07	P0001
2013	06	P0003

SELECTIONS >> YEAR=2013, MONTH=08

I need count the suppliers like this:

ID	DESCRIPTION	AMOUNT
1	NORMAL "P0002"	1
2	NEW "P0004"	1
3	REACTIVATED "P0003"	1
4	LOST "P0001"	1

My problem is how to count the ID 4.

Any Idea?

Re: how to count null records

salto — Wed, 09 Oct 2013 15:47:02 GMT

Hi,

maybe the expression could look like this:

=if(count({$<Month={$(=Max(Month))}>} Provider)>0 and count({$<Month={$(=Max(Month)-1)}>} Provider) > 0,

'Normal Provider',

if(count({$<Month={$(=Max(Month))}>} Provider)>0 and count({$<Month={$(=Max(Month)-1)}>} Provider)<=0 and count({$<Month={$(=Max(Month)-2)}>} Provider)>0,

'Reactivated Provider',

if(count({$<Month={$(=Max(Month)-1)}>} Provider)>=0 and count({$<Month={$(=Max(Month))}>} Provider)<=0,

'Lost Provider',

if(count({$<Month={$(=Max(Month)-1)}>} Provider)<=0 and count({$<Month={$(=Max(Month))}>} Provider)>0,

'New Provider')

Hope this helps.

Re: how to count null records

swuehl — Wed, 09 Oct 2013 15:51:37 GMT

Maybe like this:

=count( {<Provider= e({<Month ={08}, Year = {2013}>})*p({<Month ={07}, Year = {2013} ) >} DISTINCT Provider)

i.e. using set analysis with implicite field value definitions ( i.e. p() and e() functions).

Re: how to count null records

Gysbert_Wassenaar — Wed, 09 Oct 2013 16:24:51 GMT

See attached example

Re: how to count null records

MarcoARaymundo — Wed, 09 Oct 2013 16:36:11 GMT

Tks SALTO, but I did that and it did not work.

Re: how to count null records

Wed, 09 Oct 2013 16:44:48 GMT

Use the NullCount() function.

Re: how to count null records

MarcoARaymundo — Wed, 09 Oct 2013 16:50:34 GMT

tks G Wassenaar.

I have two questions:

1) When I select another month (ex 05), the provider P0005 in this month is a new provider and not reactivated. How to can resolved that?

2) In my example I have determined the "status" of providers, but in my app "status" is not a field, it is a calculated dimension. Applying the example you did in one dimension?

Re: Re: how to count null records

Gysbert_Wassenaar — Wed, 09 Oct 2013 18:10:06 GMT

1. See attached updated qvw.

2. I have no idea what you're trying to ask.

Re: Re: Re: how to count null records

MarcoARaymundo — Wed, 09 Oct 2013 19:28:02 GMT

Very Nice!

But, i think create a dimension STATUS can work better, see attached please.

Its possible put your expressions to work in dimension?

I need to better understand how to work with Indirect Set Analysis, is still confusing to me.

Thanks!