6 Replies Latest reply on Apr 28, 2015 7:43 AM by Daniel Hassler

# ISNULL in calculation

I am attempting to use to Tableau to aggregate all of our admissions data. With the data source that I currently have access to, I get a single row of data for each student. Because of this, I have to make assumptions and double count students (e.g. if a student is admitted, they must have applied, etc.) This all seems to work with one exception. The formula I am using is below and the portion that seems to not be functioning is in bold:

IF [CAMPUS] = "SPOK" THEN

IF [PROG_ACTION] = "ADMT" THEN 1

ELSEIF [PROG_ACTION] = "COND" THEN 1

ELSEIF [PROG_ACTION] = "DDEF" THEN 1

ELSEIF [PROG_ACTION] = "RAPP" THEN 1

ELSEIF [PROG_ACTION] = "MATR" THEN 1

ELSEIF [PROG_ACTION] = "WAPP" THEN

IF NOT ISNULL([PROG_REASON]) THEN 1

END

IF [PROG_REASON] = "NODP" THEN 1

END

END

END

Here is what I want to happen. If a student has a PROG_ACTION of WAPP, they should only be counted if they also have a PROG_REASON (there are a variety of options here). I feel like this is written correctly, but I'm obviously missing something.

I have attached an extract of my workbook. The calculated field in question is "Count of Spokane Admits".

Dan

• ###### 1. Re: ISNULL in calculation

HI Daniel

Don't you need an 'and' in there somewhere....

IF [PROG_ACTION] = 'WAPP' AND [PROG_REASON] <> null THEN 1 ELSE...

I will take a look later, just wanted to share my first thought

Cheers

Mark

• ###### 2. Re: ISNULL in calculation

Daniel,

What I see is that what you call a null value is in fact a blank field containing a single space.

You can try

IF [CAMPUS] = "SPOK" THEN

IF [PROG_ACTION] = "ADMT" THEN 1

ELSEIF [PROG_ACTION] = "COND" THEN 1

ELSEIF [PROG_ACTION] = "DDEF" THEN 1

ELSEIF [PROG_ACTION] = "RAPP" THEN 1

ELSEIF [PROG_ACTION] = "MATR" THEN 1

ELSEIF [PROG_ACTION] = "WAPP" THEN

IF ([PROG_REASON]) != ' ' THEN 1

END

IF [PROG_REASON] = "NODP" THEN 1

END

END

END

Michel

1 of 1 people found this helpful
• ###### 3. Re: ISNULL in calculation

I think those two syntaxes are the same, Mark, but I was wondering tyhe same thing.  So I would like to see Daniel give that a try.

• ###### 4. Re: ISNULL in calculation

I'm looking at this.

I could delve into the data and unpack your workbook to figure out what's happening, but it would help me a lot if you stated what you are seeing that you don't want to see, and what you actually want to see.  WHich row on the sheet is the WAPP row, for instance...

• ###### 5. Re: ISNULL in calculation

Your logic is fine  (although that line could have been simplified to just

ELSEIF [PROG_ACTION] = 'WAPP' AND NOT ISNULL([PROG_REASON]) THEN 1

but your real problem is that in your Data Source, PROG_REASON is never Null.  That's because the "blank" entries are apparently a single space rather than an actual Null.

Let me tell you how I found it.

I made a Calculated Field containing

'**' + [PROG_REASON] + '**'

and then plopped that out onto a blank Worksheet.  That showed me a blank space between the double asterisks

Armed with that knowledge, I changed your field "Count of Spokane Admits" to

IF [CAMPUS] = "SPOK" THEN

IF [PROG_ACTION] = "ADMT" THEN 1

ELSEIF [PROG_ACTION] = "COND" THEN 1

ELSEIF [PROG_ACTION] = "DDEF" THEN 1

ELSEIF [PROG_ACTION] = "RAPP" THEN 1

ELSEIF [PROG_ACTION] = "MATR" THEN 1

ELSEIF [PROG_ACTION] = 'WAPP' AND [PROG_REASON] <> ' ' THEN 1

IF [PROG_REASON] = "NODP" THEN 1

END

END

END

and that changed the result from 46 to 33.

.

• ###### 6. Re: ISNULL in calculation

Thank you all so much!

Dan