3 Replies Latest reply on Nov 27, 2016 7:22 AM by Marc MOUSSA

# How to color code the most profitable and least profitable month of each year based on the total no of profitable days?

I have created a bar chart using the super stores data to show the no of profitable days in each month of the year. Now I want to show the month with the most no of profitable days and the one with the least no of profitable days in different colors. For this I used the rank function.

if Rank(Sum(Profit)) =1  then "Most Profitable month" elseif Rank(Sum(Profit)) =12 then "Least Profitable month" end

Now for one year 2012 , the lowest no of profitable days is for May (only 13 days), but my graph shows the month of march (15 days) as the least one. This is because in the rank function I have used profit to calculate the rank and profit for the march month is more than that for May . Actually I wanted to rank based no t on the profit but on the no of profitable days . Is there any function in tableau to do this?  Please guide.

• ###### 1. Re: How to color code the most profitable and least profitable month of each year based on the total no of profitable days?

Here's a solution using 3 fields :

- days

- number of profitable days

- Rank : a custom one that handles same rank

Hope this helps

• ###### 2. Re: How to color code the most profitable and least profitable month of each year based on the total no of profitable days?

Hi Marc,

I am not able to open the workbook. Can you please type in  here the calculations you mentioned ?

Thanks

• ###### 3. Re: How to color code the most profitable and least profitable month of each year based on the total no of profitable days?

Here's the result followed by the used fields :

Days :

DATETRUNC('day', [Date de commande])

Number fo profitable Days :

SUM(IIF({ INCLUDE [Days] : SUM([Bénéfice]) }>0,1,0))

Rank :

IF [Number fo profitable Days] =

MIN({ EXCLUDE DATEPART('month', [Date de commande]) : MIN({ INCLUDE  DATEPART('month', [Date de commande]) : [Number fo profitable Days]}) })

THEN

1

ELSEIF

[Number fo profitable Days] =

MIN({ EXCLUDE DATEPART('month', [Date de commande]) : MAX({ INCLUDE  DATEPART('month', [Date de commande]) : [Number fo profitable Days]}) })

THEN

3

ELSE

2

END