7 Replies Latest reply on Jul 5, 2016 11:05 PM by Mark Fraser

    IF/AND/THEN Statements

    Stephanie Beadman

      Hi all,

       

      I need to know how many contacts have a patient with a Date PUM but *not* a First Ship Date.

       

      Initially, I wanted to create a statement that says this:

       

      IF [Type of Date]="First Ship Date" AND ISNULL [Date]

      AND [Type of Date]="Date PUM" AND NOT ISNULL [Date]

      THEN [Contact ID]

      END

       

      But this doesn't work.

       

      In other words...If there's no First Ship Date but there is a Date PUM then tell me who the contact is and how many contacts are like that. Bear in mind that some contacts have patients with no Date PUM and no First Ship Date.

       

      Thanks in advance for any help! Sample Tableau workbook attached. The sheet called "Contacts with FSD" has the opposite info - how many contacts have a patient with a First Ship Date (easier since ALL patients have a Date PUM if they have a First Ship Date).

       

      Stephanie

        • 1. Re: IF/AND/THEN Statements
          Shawn Wallwork

          Just glancing at this you put in both ISNULL([Date]) AND NOT ISNULL([Date]) they can't both be true at the same time. So you won't get any results returned.

           

          --Shawn

          • 2. Re: IF/AND/THEN Statements
            Dmitry Chirkov

            Yes, you probably need something like IF (COND1 AND COND2) OR (COND3 AND COND4).

            • 3. Re: IF/AND/THEN Statements
              Mark Fraser

              Hi Stephanie

               

              Do you understand what you need?

              In other words...If there's no First Ship Date but there is a Date PUM then tell me who the contact is and how many contacts are like that.

              First Ship Date = NULL and Date PUM <> NULL

              That's OK... but then

              Bear in mind that some contacts have patients with no Date PUM and no First Ship Date.

              so Date PUM = NULL and First Ship Date = NULL

              But earlier we used Date PUM <> NULL as our test?!

               

              As Shawn mentions - for logical statements they need to have a single state, not 2 at the same time.

              Something is either empty or not.

               

              What you're asking for should be achievable, but we (and maybe you) really need to understand first

              Consider the below - (it may not be perfect but...) - we have all states...

              both dates populated (id 1 +3 (both ways round))

              no dates (2)

              only ship (4)

              only PUM (5)

               

              How should we deal with each id/ state?

               

              Cheers

              Mark

              • 4. Re: IF/AND/THEN Statements
                Stephanie Beadman

                Mark,

                 

                I am looking to count the number of contacts with patients who are in state

                #5.

                 

                You'll also notice the data is pivoted (fields called Date Type and Date

                were pivoted). Perhaps they should be unpivoted to achieve what I want. In

                excel, it would be as simple and filtering out all patients with a "null"

                first ship date, then filtering out all patients with a "null" date PUM and

                then taking the list of contacts associated with those patients, de-duping

                them, and counting them.

                 

                I understand exactly what I need. Perhaps if statements aren't the way to

                go. Do you understand?

                 

                Stephanie

                 

                On Jul 5, 2016, at 3:24 AM, Mark Fraser <tableaucommunity@tableau.com>

                • 5. Re: IF/AND/THEN Statements
                  Mark Fraser

                  Hi Stephanie

                   

                  Cool, we're getting there

                   

                  This is important

                  You'll also notice the data is pivoted (fields called Date Type and Date

                  were pivoted). Perhaps they should be unpivoted to achieve what I want.

                  The answer is yes, yes, yes!

                  It is much easier to work with dis-aggregated data in Tableau, do the aggregation in Tableau not before.

                  In excel, it would be as simple and filtering out all patients with a "null"

                  first ship date

                  Tableau is exactly the same...

                  You'd simply have this test IF ISNULL([first ship date]) THEN ... ELSE ... END

                  then filtering out all patients with a "null" date PUM and

                  Here is where I get confused (again), they should have a PUM date or not?!

                  (earlier you said number 5, first ship = null, PUM <> NULL, hence my confusion - they have to be either null or not null)

                   

                  and then taking the list of contacts associated with those patients, de-duping

                  them, and counting them.

                  This will be on the true return of the IF

                  COUNTD() will get a distinct of patients (i.e. de-duped) and count them.

                   

                  Could you provide a small fake sample (in Excel) with expected output?

                  If anything above isn't clear, do ask!

                   

                  Cheers

                  Mark

                  • 6. Re: IF/AND/THEN Statements
                    Stephanie Beadman

                    I unpivoted the data and wrote an if statement that works. Made it a little easier (although the pivot is better for some other charts I'm making).  Thanks for setting me on the right track!

                    • 7. Re: IF/AND/THEN Statements
                      Mark Fraser

                      Hi Stephanie

                       

                      Thanks for letting us know the good news

                      And best of luck with the rest of your project!

                       

                      Please use the forums again if you need further help, or just want to learn more

                       

                      Cheers

                      Mark