6 Replies Latest reply on Dec 6, 2016 7:30 AM by Timothé Le Vigouroux

# How to use true False statement depending on conditional statement?

Hi,

I am looking for help on how to use a case statement to condition boleans.

I want to create a bolean in order to define wether a client has been visited according to following rule:

Client that have a turnover up to 900000 should be visited at least once a month

Client that have a turnover up to 100000 should be visited at least once for 2 months

Client that have a turnover below 100000 should be visited at least once for 3 months

Turnover (fact): Amount of turnover from transactions in regards to specific clients

HistoryID (dimension): Meeting ID allow me to identified number of visit via a countD formula

Here is how I turn it in order to have a bolean statement depending on turnover amount:

Case sum([Turnover])

When >= 900000 then COUNTD([Historyid])>= 12/ DATEPART('month',TODAY())

When >= 100000 then COUNTD([Historyid])>= 6/ DATEPART('month',TODAY())

When < 100000 then COUNTD([Historyid])>= 3/ DATEPART('month',TODAY())

else COUNTD([Historyid])>= 3

END

My formula seems not to be valid (syntax issue). Would anyone could help me to correct my formula?

Thanks a lot,

Timothé

• ###### 1. Re: How to use true False statement depending on conditional statement?

Hello Timothe,

If you are looking for the boolean statement, after creating the cal field drop that into filter shelf so that it will show you true/false.

Can you plz attach the workbook so that we can solve the cal without any errors?

Thanks,

Triveni.

1 of 1 people found this helpful
• ###### 2. Re: How to use true False statement depending on conditional statement?

if you are referring to the current month then

first determine how many visits should customer have (you need to apply your logic - whether this turnover should be total or last month etc)

VisitsPlanned

Case sum([Turnover])

When >= 900000 then 12

When >= 100000 then 6

When < 100000 then 3

else 3

END

then calculate how many visits really took place in the month (same as above - apply time logic)

VisitsDone:

COUNTD([Historyid])

MeetCriteria:

if visitsDone>VisitsPlanned then 1 else 0 end

• ###### 3. Re: How to use true False statement depending on conditional statement?

This is close (and would work in SQL) but Tableau can't do boolean algebra in a case statement, you will need to do this in an IF THEN ELSEIF THEN statement.

VisitsPlanned

IF sum([Turnover]) >=900000 THEN 12

ELSEIF sum([Turnover]) >=100000 THEN 6

ELSE 3

END

To set the Target then as you had before (assuming we are doing this within a single calendar year, otherwise the calculation is more complicated)

VisitsDone

countd([Historyid])/ DATEPART('month',TODAY())

then save yourself an extra IF statement by doing the below (Tableau will give you True/False in return)

VisitsDone>VisitsPlanned

if you want 1 and 0  then int(VisitsDone>VisitsPlanned)

1 of 1 people found this helpful
• ###### 4. Re: How to use true False statement depending on conditional statement?

true for the if

but I would take caution with this countd([Historyid])/ DATEPART('month',TODAY()). i think it's worth to add some logic in the first if statement like

VisitsPlanned

IF sum([Turnover]) >=900000 THEN 12 * month(today())/12

ELSEIF sum([Turnover]) >=100000 THEN 6 * month(today())/12

ELSE 3 * month(today())/12

END

because otherwise those guys handling >90000 will always have VisitsDone<VisitsPlanned until end of the year

2 of 2 people found this helpful
• ###### 5. Re: How to use true False statement depending on conditional statement?

really good point Maciek!

• ###### 6. Re: How to use true False statement depending on conditional statement?

Thanks a lot for your prompt feedback it made my day :-)