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.
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.
Book3.twbx 1.2 MB
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.
Hmm, I'm not sure what you mean. Can you share a packaged workbook or give me more information?
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.
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.
Thank you !!