So yes you'll need to create a number ordering for this (initially I hoped we could parse out the first month, in each, convert to a date and sort on that...but the order is SEPT, DEC, MAR...so not in month order!)
in which case you can use the following formula to create an order dimension
WHEN "SEPT-DEC" THEN 1
WHEN "DEC-MAR" THEN 2
WHEN "MAR-MAY" THEN 3
WHEN "MAY-JULY" THEN 4
WHEN "JULY-SEPT" THEN 5
and then you can bring this in (as a dimension and blue pill) to the left of your contract field to sort
In your final version, you can hide the header for [Contract Order]
Hope that helps.
SEPT-DEC DEC-MAR MAR-MAY MAY-JULY JULY-SEPT
Thank you Simon for your reply.
If I had only few contracts then i could do it like you said. But my data is dynamic and every week new contact will add or subtract from the data. And moreover I've many spreads and each spreads has many contacts. So hard code is not possible here. Other than adding the column to my db is there any way to do it in tableau?
Attached the sample tableau page.
month_count.twbx 268.7 KB
So if we have a rule on the way we want them sorted (so a general rule, rather than hard-coding) then we should be able to do this in Tableau. Attached is an example. In this I've pulled out the 1st Month occurrence from the [Contract] field
and then used this to sort.
So we have this
If we look at 'BO Spread'...this method puts DEC-JAN to the end, as DEC is the last month in the sequence...however in your original post you had DEC-JAN first. If this was a rule (say if 2nd Month occurrence, in this case 'JAN') the start with this month, we could build this extra rule in....so something like this
[Contract (Month) JAN END First]
IF DATEPART('month',DATEPARSE('dd/MMM/yy','01/'+RIGHT([Contract],3)+'/16')) = 1 THEN 0 ELSE 1 END
we can then bring this field in before the [Contract (Month)] and so force it to sort by then first
Hopefully you can build up the rules you need from this method, but if not let me know the rules, and I can help work up the formula(s) to do it.
month_count_SR.twbx 291.7 KB
Thank you again for your response with the workbook.
But it is not the solution for my problem.These are future contracts means currently it is August month so my contract should start from Aug or Sept or next coming month. After Dec it falls into next year(2017). How to give the numbers to the months?
These are future contracts means currently it is August month so my contract should start from Aug or Sept or next coming month. After Dec it falls into next year(2017)
OK, so I wasn't aware of that (not quite perfected my mind-reading skills just yet)....
So yes we need a definitive rule set, so we can code how we want them sorted...much as you'd have done if you baked it into the SQL query (or ETL)...so anything (excluding iterative scripts) that we can do in them we should be able to do in Tableau (...famous last words!!)
So my understanding of the rule is (I will refer to 1st Month as the First part of the contact string, and 2nd Month as the 2nd part...so JAN - FEB 1st Month = JAN and 2nd Month = FEB);
From the Month we're in (currently August) -> we take the next 1st Month we have in position 1...we then run through the 1st months sequentially. When we reach a DEC 1st Month, it starts again...
There are a few questions...what Month do we start with if the current month is DEC (and we have no DEC 1st Month)? (I assume we start with JAN?)...do we ever have over-lapping periods, so JAN-MAR and FEB-APR? (if so how do we handle these). I also noticed in the fields we sometimes have just a 1st month (so just APR), and sometimes 3 strings (JAN-FEB-MAR)...would these example be APR-APR, and JAN-MAR to keep them consistent? Do we ever have more than a year of contract stings (i.e. so JAN-FEB could appear twice)?
If you let me know on the above I'll have a little play and see if we can build up the logic
...so how's this (not withstanding any exception handling!)
If you can take a look and let me know if/where it is wrong, and I can think about how to account for those.
If it's all good, I'll run through (assuming I can remember how I did it!!) how it all works!
month_count_ordering_SR.twbx 286.7 KB
Thank you so much. Yeah , this is what i wanted to do.
Your assumption is correct. If the current month is DEC then the first month should be DEC if there is no DEC then it should be JAN.
There are 3 types of contracts if Spread then the contract is between 2 months(SEP-OCT), If Fly then 3 months(SEP-DEC-MAR) and if it is RV then single month(AUG).
Thank you again.This is really a great community. Someone will be there to help us when we need it.
1 of 1 people found this helpful
Now for the hard bit...how does it all work!
So the main idea is to take advantage that Blue Pills (to the left of something) will sort it in ascending order....
So first I wanted someway to order the [contracts]...so I took the left part of the string, turned it into a date and then took the Month (as a number...so Jan = 1, Feb = 2...etc.)
[Contract (Month) 1st Month]
the year here is irrelevant, as I only want the month number
I did the same with the current month
[Contract (Month) Current Month]
so I now have the month we are in, 8, and the number of the month for the 1st part of each [contract] string.
I then took the difference between these 2
[Current to 1st DIFF]
[Contract (Month) 1st Month]-[Contract (Month) Current Month]
so this means that if the month is in the future (from current month) it's positive, and negative otherwise.
So I created the following 2 calcs. The first one, is for future (or current) month dates where the diff is +ve. This is the [contract] we want to order first
[Contract Order + ve]
IF[Current to 1st DIFF]>=0 THEN ([Current to 1st DIFF]) ELSE 100 END
so by setting any ones that aren't +ve to 100 they get sorted after the +ve ones do, and the positive ones get sorted by the order of how close they are to the current month (so AUG-SEPT would return 0 and so be first)
The next one if for where the difference is -ve. This means the month has already passed (this year), so will get ordered (again by how far they are away from now...so furthest away, negatively speaking, goes first)
[Contract Order - ve]
IF[Current to 1st DIFF]<0 THEN ([Current to 1st DIFF]) ELSE 100 END
and as we put the -ve blue first, and then -ve blue pill second, we force this nested sort.
...that was a fun one (...although my brain is in a bit of pain!!).
Glad it helped...and yes the best thing about Tableau is the community
Thank you Simon for taking out your precious time to resolve my problem and to write the procedure in details. You are genius.
You are genius.
...actually I stumbled across the solution while having a play (one of those nice happy accidents!...it's better to be lucky than smart, I guess )
...but I'll take the genuis tag!! Thanks