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

    ISNULL in calculation

    Daniel Hassler

      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

          ELSEIF [PROG_ACTION] = "WADM" THEN

              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".

       

      Thank you in advance for your help!

      Dan

        • 1. Re: ISNULL in calculation
          Mark Fraser

          HI Daniel

           

          Just reading your question...

          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
            Michel Caissie

            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

                ELSEIF [PROG_ACTION] = "WADM" THEN

                    IF [PROG_REASON] = "NODP" THEN 1

                    END

                END

            END

             

            Michel

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

              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
                Joe Oppelt

                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
                  Ken Patton

                  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

                      ELSEIF [PROG_ACTION] = "WADM" THEN

                          IF [PROG_REASON] = "NODP" THEN 1

                          END

                      END

                  END

                   

                  and that changed the result from 46 to 33.

                   

                   

                  .

                  • 6. Re: ISNULL in calculation
                    Daniel Hassler

                    Thank you all so much!

                     

                    Dan