Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

MarcoARaymundo
Contributor III

how to count null records

Hi!

Table A

YEARMONTHPROVIDER
201308P0004
201308P0003
201308P0002
201307P0002
201307P0001
201306P0003

SELECTIONS >> YEAR=2013, MONTH=08

I need count the suppliers like this:

IDDESCRIPTIONAMOUNT
1NORMAL "P0002"1
2NEW "P0004"1
3REACTIVATED "P0003"1
4LOST "P0001"1

My problem is how to count the ID 4.

Any Idea?

Tags (4)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: how to count null records

See attached example


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
salto
Valued Contributor II

Re: how to count null records

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.

MVP
MVP

Re: how to count null records

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

MVP & Luminary
MVP & Luminary

Re: how to count null records

See attached example


talk is cheap, supply exceeds demand

View solution in original post

MarcoARaymundo
Contributor III

Re: how to count null records

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

Not applicable

Re: how to count null records

Use the NullCount() function.

MarcoARaymundo
Contributor III

Re: how to count null records

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?

MVP & Luminary
MVP & Luminary

Re: Re: how to count null records

1. See attached updated qvw.

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


talk is cheap, supply exceeds demand
MarcoARaymundo
Contributor III

Re: Re: Re: how to count null records

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!