4 Replies Latest reply on Jan 6, 2015 1:36 PM by Rick James

# Simple question(s) - Count Null or Blanks and adding calc fields

Hi - I am new to Tableau and have stumbled upon what seems to be easy to do but I am just banging my head and can't seem to figure it out -

Problem 1: I want to count null or blank boxes using just a field in Measures section - in my case its called "Attempted"

Is there an easy way to do so? Count or CountD seems to eliminate nulls but I need this to be reversed and count the actual blanks or nulls in dataset?

Problem 2: I have a calculated filed that is based on other calculations - "Total AA & AW" & "Total P1, P3, P4, PB" that I want to show value by adding their individual calculated fields. I can't seem do to this even if when I add SUM() between the calcs. You can see that it works fine for "Total IS & AS" because I wrote IF/THEN statement for it? Do I need to do this for other 2 Total fields as well?

Problem/Solution: I have one more calculated field "Future" in my workbook - can you verify if the calc is correct - what I am wanting to show in "Future" field that it should Filter on "Start Date" and count all "Number of Records" all after current date - is my approach correct?

Thank you for your help in advance. I have attached a sample workbook.

P.S. I am sure I will have more questions as next things I am working on it calculating next month, 2 months, 3 months from now but for now this will give me some guidance.

• ###### 1. Re: Simple question(s) - Count Null or Blanks and adding calc fields

Rick:

Welcome to Tableau and the Community !

Problem 1 : Use isnull function to check if the given value is null or not and then sum them

Problem 2: You need to aggregate them

Problem/Solution: Your calculation works, but I normally use Date functions (datediff) in this case.

Hope this helps and see the attached.

With new measures at the bottom:

..kk

• ###### 2. Re: Simple question(s) - Count Null or Blanks and adding calc fields

Thank you KK - so when you did datediff - there is no need to add "Number of Records" in the calc - based on your calc it seems to work but just wondering how is that possible? is Tableau calculating the rows in the back-end even without defining "Number of Records"?

In my case it will always be 1 against each row as Count or Number of Records but just curious to know. Thank you!

• ###### 3. Re: Simple question(s) - Count Null or Blanks and adding calc fields

Rick:

With the datediff I am doing a row level calculation and setting the value to 1 based on the condition. When I drag the measure, the numbers are summed as it is aggregating. You don't need number of records.

Hope this helps.

..kk

1 of 1 people found this helpful
• ###### 4. Re: Simple question(s) - Count Null or Blanks and adding calc fields

Thanks - yes it does help in understanding how datediff works. I am sure I will have to read more on this.