12 Replies Latest reply on Oct 19, 2017 7:58 AM by mohsin.khan.1

# How does if-elseif condition works on tableau

My case like:

IF [total  sales] > 2000 and [total  sales] <= 3000 then [total  sales] * 0.025

ELSEIF  [total  sales] > 3000 and [total  sales] <= 4000 then [total  sales] * 0.03

ELSEIF  [total  sales] > 4000  and [total  sales] <= 80000 then [total  sales] * 0.035

ELSEIF  [total  sales] > 80000  and [total  sales] <= 100000 then [total  sales] * 0.04

ELSEIF  [total  sales] > 100000  and [total  sales] <= 120000 then [total  sales] * 0.045

ELSEIF  [total  sales] > 120000 THEN [total  sales] * 0.05

ELSE  0 END

Its always satisfy first condition or else condition. why this case not satisfy other conditions?

• ###### 1. Re: How does if-elseif condition works on tableau

Hi Prakhar,

Quite difficult to tell without looking at your data. Try wrapping  your measure as sum(total sales) if it not already aggregated. Else, share a sample workbook.

~ Tushar

1 of 1 people found this helpful
• ###### 2. Re: How does if-elseif condition works on tableau

Hi on the surface the formula looks like it is right but I have a question - is [Total Sales] and aggregated value i.e it is the sum of sales?

if it is not already aggregated then try wrapping it in sum() like Sum([total sales])

Also the if statement works sequentially and the first True claus will trigger the Then clause - if it is Fales the statement goes to the Elseif ...until it finds a true

That said you can greatly simplify you expression by

IF [total  sales] > 2000 and [total  sales] <= 3000 then [total  sales] * 0.025

ELSEIF   [total  sales] <= 4000 then [total  sales] * 0.03

ELSEIF   [total  sales] <= 80000 then [total  sales] * 0.035

ELSEIF   [total  sales] <= 100000 then [total  sales] * 0.04

ELSEIF   [total  sales] <= 120000 then [total  sales] * 0.045

ELSEIF  [total  sales] > 120000 THEN [total  sales] * 0.05

ELSE  0 END

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: How does if-elseif condition works on tableau

Hey

My measure is already aggregated.

I also tried your solution but it's doesn't worked.

Thank you for your effort.

• ###### 4. Re: How does if-elseif condition works on tableau

Hey

My measure is already aggregated.

Thank you for your effort.

• ###### 5. Re: How does if-elseif condition works on tableau

Hi Prakar,

Using Superstore datasource, Did some analysis and it work perfect using your formula.

Formula:01--Sales Ratio

IF SUM([Sales])>30000 and SUM([Sales])<80000 THEN SUM([Sales])*0.01

ELSEIF SUM([Sales])>80000 and SUM([Sales])<110000 THEN SUM([Sales])*0.02

ELSEIF SUM([Sales])>110000 and SUM([Sales])<150000 THEN SUM([Sales])*0.03

ELSEIF SUM([Sales])>150000 and SUM([Sales])<200000 THEN SUM([Sales])*0.04

ELSEIF SUM([Sales])>200000 and SUM([Sales])<400000 THEN SUM([Sales])*0.05

ELSE 0.06 END

Formula:01--Sales Ratio

IF ([Sales])>30000 and ([Sales])<80000 THEN ([Sales])*0.01

ELSEIF ([Sales])>80000 and ([Sales])<110000 THEN ([Sales])*0.02

ELSEIF ([Sales])>110000 and ([Sales])<150000 THEN ([Sales])*0.03

ELSEIF ([Sales])>150000 and ([Sales])<200000 THEN ([Sales])*0.04

ELSEIF ([Sales])>200000 and ([Sales])<400000 THEN ([Sales])*0.05

ELSE 0.06 END • ###### 6. Re: How does if-elseif condition works on tableau

Please post your workbook so we can see what is going on  if you are concerned about confidential data see the link Anonymize your Tableau Package Data for Sharing

Thanks

Jim

• ###### 7. Re: How does if-elseif condition works on tableau

I upload my file.Please have a look

• ###### 8. Re: How does if-elseif condition works on tableau

I upload my file.Please have a look

• ###### 9. Re: How does if-elseif condition works on tableau

Hi Prakhar,

I think your issue was that you didn't wrap your total calcs with SUM(). See attached, where I've added this into your formula and called this total sales * n (and have also included another calculated field which defines what n is, so that it's clearer in your table, i.e. you can see total sales column, you can see n, and you can see the product of these two columns being the calc you wanted).

IF sum([total  sales]) > 2000 and sum([total  sales]) <= 3000 then sum([total  sales]) * 0.025

ELSEIF  sum([total  sales]) > 3000 and sum([total  sales]) <= 4000 then sum([total  sales]) * 0.03

ELSEIF  sum([total  sales]) > 4000  and sum([total  sales]) <= 80000 then sum([total  sales]) * 0.035

ELSEIF  sum([total  sales]) > 80000  and sum([total  sales]) <= 100000 then sum([total  sales]) * 0.04

ELSEIF  sum([total  sales]) > 100000  and sum([total  sales]) <= 120000 then sum([total  sales]) * 0.045

ELSEIF  sum([total  sales]) > 120000 THEN sum([total  sales]) * 0.05

ELSE 0

END

Hope that solves the question appropriately!

Mohsin

• ###### 10. Re: How does if-elseif condition works on tableau

Thank you Mohsin Khan.

Your calculation helped me.

Thank you so much..

• ###### 11. Re: How does if-elseif condition works on tableau

Hey Mohsin,

I have one question, How does you make new measures (like n and total * n) in measures section.

• ###### 12. Re: How does if-elseif condition works on tableau

Hi Prakhar,

New measures are simply calculated fields that Tableau identifies as numbers, and so auto-dumps it in the measures section. You can right click on it and change it to a dimension if you wish, and change the data type, if, for example, you had a number, but wanted to use it as discreet values - you could make it a dimension and change the data type to string (and visa versa of course if you wanted to shift a dimension into the measures section).