Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Count Null Fields

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Count Null Fields

Maybe like attached?

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

4 Replies
MVP
MVP

Re: Count Null Fields

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.

Not applicable

Re: Count Null Fields

I reattached the corrected the qvw.  Sorry about that.

MVP
MVP

Re: Count Null Fields

Maybe like attached?

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

Not applicable

Re: Count Null Fields

Thank you.  Your solution worked!

Community Browser