11 Replies Latest reply on Aug 24, 2016 12:38 PM by Simon Runc

    Month sorting

    Manjula Raj

      Hi,

       

      I've a column in my data sheet as contract which is a period between 2 months like sep-dec, dec-mar,etc. I want to sort it according to my date selection.

       

      The data looks like this:

      SpreadContract
      C SpreadDEC-MAR
      JULY-SEPT
      MAR-MAY
      MAY-JULY
      SEPT-DEC

       

      Suppose my date selection is starts from 5-8-2016, the I want the contact to be sorting like below.

       

      SpreadContract
      C SpreadSEPT-DEC
      DEC-MAR
      MAR-MAY
      MAY-JULY
      JULY-SEPT

       

      I think adding a column by giving a number to the months  can do the work for me. But i'm not sure how to do that. Please help me.

       

      Thanks,

      Manjula

        • 1. Re: Month sorting
          Simon Runc

          hi Manjula,

           

          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

           

          CASE [Contract]

              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

          END

           

          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
          • 2. Re: Month sorting
            Manjula Raj

            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.

             

            Thanks again

            • 3. Re: Month sorting
              Simon Runc

              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

               

              [Contract (Month)]

              DATEPART('month',DATEPARSE('dd/MMM/yy','01/'+LEFT([Contract],3)+'/16'))

               

              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.

              • 4. Re: Month sorting
                Łukasz Majewski

                Isn't this a case for manual sorting?

                 

                Provided your 'contracts' period follow a naming convention and is not random.

                • 5. Re: Month sorting
                  Manjula Raj

                  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?

                  • 6. Re: Month sorting
                    Simon Runc

                    hi Manjula,

                     

                    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

                    • 7. Re: Month sorting
                      Simon Runc

                      ...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!

                      2 of 2 people found this helpful
                      • 8. Re: Month sorting
                        Manjula Raj

                        Hi Simon,

                         

                        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.

                        • 9. Re: Month sorting
                          Simon Runc

                          Awesome!

                           

                          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]

                          DATEPART('month',DATEPARSE('dd/MMM/yy','01/'+LEFT([Contract],3)+'/16'))

                           

                          the year here is irrelevant, as I only want the month number

                           

                          I did the same with the current month

                          [Contract (Month) Current Month]

                          DATEPART('month', today())

                           

                          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

                          1 of 1 people found this helpful
                          • 10. Re: Month sorting
                            Manjula Raj

                            Thank you Simon for taking out your precious time to resolve my problem and to write the procedure in details. You are genius.

                            • 11. Re: Month sorting
                              Simon Runc

                              No problem.

                               

                              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