5 Replies Latest reply on Jul 29, 2013 1:49 PM by Jonathan Drummey

    Custom Quarters?

    Chris Tsui

      Hi All,

       

      I have run into a little bit of a calculation issue that I'm hoping I can get some help with.  I've mocked up a simple example using the Superstore extract.

       

      I laid out Order Year, Order Date in the columns shelf

      and Ship Year, Ship Quarter, Ship Month in the rows shelf

      and Sum Order Quantity in the details shelf.

       

      My requirement is to create a "custom shipping quarter" which is the Quarter plus the 1 following month.

      So:

      Q1 + April

      Q2 + July

      Q3 + October

      Q4 + Jan (Of next year)

       

      The hope would be the layout looking simalar to:

       

      Order Q1Order Q2Order Q3Order Q4Order Q1
      Sum(Order Quantity where Ship Q1 + April)Sum(Order Quantity where Ship Q2 + July)Sum(Order Quantity where Ship Q3 + October)Sum(Order Quantity where Ship Q4 + Jan)Sum(Order Quantity where Ship Q1 + April)

       

      My thought was to create a custom quarter "bucket" where I could group all the Shipment months into 4 month "quarters" vs. the traditional 3 month quarters (Maybe some sort of case statement... when month = 1,2,3,4  etc.) but where I run into a hiccup is when I get to Q4 I need to add some form of additional check to include month 1 of the next year?  However with the way the table is laid out I think the "next" month seems to be out of context, which I think brings us into table calculation territory.

       

      does anyone have any thoughts on how this may be achieved?  It'd be greatly appreciated!

        • 1. Re: Custom Quarters?
          Chris Tsui

          I'm begining to think this may be a Table calculation as it has to look "accross" the columns to grab the next quarter's ship month?  Does that sound right?

          • 2. Re: Custom Quarters?
            Tracy Rodgers

            Hi Chris,

             

            I just took a look at this and my knee **** was a table calculation as well--just haven't come up with the solution yet.

             

            I'll work on it and see what I come up with.

             

            -Tracy

            1 of 1 people found this helpful
            • 3. Re: Custom Quarters?
              Jonathan Drummey

              Hi Chris,

               

              Personally, I find the view easier to read if I swap Rows and Columns so the Order Date is on Rows, that way I can read across for all the given Orders in Q1, I can see how many shipped in Jan, Feb, Mar, Apr of Q2. It's also possible to see in this layout that there are no orders that shipped for a given quarter later than the month after the quarter, which means that the data may not be sufficient to test edge cases. For example,  what do you want to do about an order on March 31 that shipped over a month late, i.e. shipped in May, after the Q1+April bucket ends?

               

              Do the shipping date dimensions need to be in the view? If they don't, you could do the above computation with a row-level calc that did something like [Ship Date] >= DATETRUNC('quarter',[Order Date]) and [Ship Date] <= (DATEADD('month',4,DATETRUNC('quarter',[Order Date]))-1).

               

              Jonathan

              • 4. Re: Custom Quarters?
                Chris Tsui

                Thanks Johnathan and Tracy!

                 

                To answer your question, no the Ship date dimension doesn't need to be within the view, and I just need the count/sum of the quantities that meet the order date and ship date criteria.  So to my dismay, your solution Johnathan.....works..... in its simplicity.  I'm not sure how I didn't come across this before, and feel a tad sad because it was right in front of me

                 

                I am always amazed at some of the different perspectives I get from all the people here in the Tableau community.

                 

                On a more curiois note.  Trace (and Johnathan)..... would there be a table calc equivalent to achive a simlar result?

                 

                Or even some custom bin/set/group way?

                • 5. Re: Custom Quarters?
                  Jonathan Drummey

                  Hi Chris,

                   

                  I've made that same mistake a few times in recent weeks, so it's been more on my mind to pay attention to the possible simpler solutions!

                   

                  There almost certainly is a table calc solution, it would need the month/year of ship date in the view and the quarter of order date at a minimum, then it could be a WINDOW_SUM() calc addressing on the month/year of ship date. Given that the level of detail is greater than needed in than your original output table, then some techniques like the IF FIRST()==0 hack would need to be applied to reduce the # of results so it would look good.

                   

                  Jonathan