6 Replies Latest reply on Oct 22, 2018 2:04 AM by Ombir Rathee

# CountD where 'value' is not null

Need help with a calculation.

I'm looking to count unique number of 'customers' where 'ship date' is not null. How do I create a calculated field with that logic? I know I can add ship date as a filter and just select non null values but I'd like to create a calculated field for further analysis.

• ###### 1. Re: CountD where 'value' is not null

If you can post up a sample TWBX, it will be much simpler to help you out -- I try to avoid writing calcs without first testing them in a Workbook, so just having that starting point would be excellent.  You can take a subset of rows from Superstore Sales into Excel, and make some Ship Dates null, in order to post a TWBX here in the thread.  Cheers!

At first glance, something like:  IF NOT ISNULL(attr(Ship Date)) then COUNTD(Customer ID) END -- I just don't recall if ISNULL can be applied to dates or not.  There's also an IFNULL function that may be appropriate.

• ###### 2. Re: CountD where 'value' is not null

I did a quick sample to test.

 Customer Ship Date A 1/23/16 B 1/12/16 C 1/1/16 D 1/10/16 E F A 1/5/16 B 1/12/16 E

2 of 2 people found this helpful
• ###### 3. Re: CountD where 'value' is not null

Exactly what I needed! Thanks Adam Crahen

• ###### 4. Re: CountD where 'value' is not null

Dipesh,

In addition to the post above. Below the options and stored in attached workbook version 9.0

a. IF NOT ISNULL(attr([Shipdate])) then COUNTD([Customer]) END

b. IF ISNULL(attr([Shipdate])) then COUNTD([Customer]) END

c. IFNULL([Shipdate],#1-1-2016#)