7 Replies Latest reply on Oct 25, 2016 6:31 AM by David Li

# Ranking market thats been "green" the longest

Hi all,

My client has requested a view which shows a ranking of markets based on a KPI being in the green status.

Now Rank 1 would be the market which has had the Green for the most consecutive months etc.

I know this is possible in Tableau (version 9) but I am drawing a complete blank on how to go about it.

I would be grateful for any assistance the community can offer, and thank you in advance!

example

Net profit =1) France green for 5 months
2) Belguim 4months

• ###### 1. Re: Ranking market thats been "green" the longest

Hi Sam! You can do this with a pair of table calculations.

First, we get the consecutive positive KPI months by using the following calc. Obviously, replace [Profit] with your KPI measure.

IIF(SUM([Profit])>0, PREVIOUS_VALUE(0)+1, 0)

Then, we get the negative of the WINDOW_MAX() on that and use it as a discrete pill for sorting.

-WINDOW_MAX([Consecutive Positive])

After this is done, we can just hide the header for this pill. (I left it in so you can see what's going on.)

Tableau 9.3+ TWBX attached.

• ###### 2. Re: Ranking market thats been "green" the longest

Thank you for the fast reply.

This solution is great, however I am currently trying to do this with I dimension is there a method that would work for that?

Alternatively I can make this work, just required me re-coding the RAG into tableau rather than the logic being done in our database.

• ###### 3. Re: Ranking market thats been "green" the longest

Hmm, I'm not sure what you mean. Can you share a packaged workbook or give me more information?

• ###### 4. Re: Ranking market thats been "green" the longest

I can't attach a workbook due to the privacy.

The best way I can explain it; I have a discrete dimension (can only be a dimension) for rag, I pull this from the data and it holds either RED GREEN or AMBER. I need to be able to count the previous months where this value is consecutively green.

• ###### 5. Re: Ranking market thats been "green" the longest

Oh, I see. In that case, you can try changing the test condition so it looks for GREEN in that dimension.

IIF(ATTR([RAG]) = "GREEN", PREVIOUS_VALUE(0)+1, 0)

Then, as before, we get the negative of the WINDOW_MAX() on that and use it as a discrete pill for sorting.

-WINDOW_MAX([Consecutive])

• ###### 6. Re: Ranking market thats been "green" the longest

Thank you !!

• ###### 7. Re: Ranking market thats been "green" the longest

You're welcome!