how to count null records

[MarcoARaymundo]

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?

[salto]

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.

[swuehl]

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).

[Gysbert_Wassenaar · Accepted solution]

See attached example

[MarcoARaymundo]

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

[#6]

Use the NullCount() function.

[MarcoARaymundo]

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?

[Gysbert_Wassenaar]

1. See attached updated qvw.

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

[MarcoARaymundo]

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!