CODEWITHSUNDEEP
tableau-apicalculated-field
user3302483
user3302483

Reputation: 855

How do I build a Tableau divide function based on a case statement?

In Tableau I have a fully normalized dataset e.g. I have a field for a dimension (country) a field for metric (profit and sales) and a field for value (fact numbers).

I was wondering if I can write a case statement where I divide profit by sales e.g. in SQL it would be something similar to:

(case when metric = 'profit' then [value] else 0 ) / (case when metric = 'sales' then [value])

I've tried building the case statement using the Tableau calculated field functionality but it returns zero - I suspect because I'm trying to do this in 1 step whilst in SQL I think it might be a 2 step process.

Please note that the simple solution of re-structuring the data isn't really an option as this creates other issues (not outlined in my simplistic example).

Upvotes: 0

Views: 16991

Answers (2)

minatverma
minatverma

Reputation: 1099

I assume your dataset has profit and sales in different rows .Which might have been giving the trouble. The good news is that with Tableau 9.0 and later this can be achieved easily using LOD Calculations .

So you can try creating a calculated field as 

country_profit : {FIXED country : SUM(CASE WHEN metric = 'PROFIT' then value)}
country_sales : {FIXED country : SUM(CASE WHEN metric = 'Sales' then value)}
result : country_profit / country_sales

Let us know if this works .

Upvotes: 1

vhadalgi
vhadalgi

Reputation: 7189

Try this:

step 1: calculated field Profit:

if metric='Profit' then value end

step 2: calculated field sales:

if metric='sales' then value end

step 3 : one calculated field to divide Profit/sales

then you use this to get the result.

Upvotes: 1

Related Questions