topic Re: Count Null Fields in QlikView

Count Null Fields

Tue, 18 Jun 2013 19:12:55 GMT

I need help with my expressions. My sample data is attached in the qvw. I am trying to do two things...

1. Count # of Sales Agents who have presented and talked to 3 or more customers. This needs to be a weekly average shown at the Month level in a chart. Each presentation is identified witha unique presentation key.

Currently my formula looks like this but does not work properly:

sum(aggr(if(count([Sale Key])>=3 , 1, 0), [Sales Agent],Week))

2. Count # of Sales Agents who have 3+ presentations but did not make a sale. If there is a null in the Sale Key column then this implies the presentation did not result into a sale.

My incorrect formula:

sum(aggr(if(count([Sale Key])<=0, 1, 0), [Sales Agent], Week))

ANY HELP WOULD BE GREATLY APPRECIATED.

Re: Count Null Fields

swuehl — Tue, 18 Jun 2013 20:32:40 GMT

Do you have the correct sample attached to your post? I can't see fields [Sale Key] and [Sales Agent].

There is a field SaleID, but there are no Nulls in this field.

Re: Count Null Fields

Tue, 18 Jun 2013 20:39:09 GMT

I reattached the corrected the qvw. Sorry about that.

Re: Count Null Fields

swuehl — Tue, 18 Jun 2013 21:51:25 GMT

Maybe like attached?

Please note that your sales and presentation keys are not unique (not sure if this does matter though).

Re: Count Null Fields

Wed, 19 Jun 2013 13:39:11 GMT

Thank you. Your solution worked!