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

# IF/AND/THEN Statements

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

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

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

• ###### 3. Re: IF/AND/THEN Statements

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

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

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

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

Hi Stephanie

Thanks for letting us know the good news

And best of luck with the rest of your project!