9 Replies Latest reply on May 17, 2013 12:51 AM by Bruce Zhang

    How to make an index trend by month?

    Bruce Zhang

      Hi all,

      This is my first time to ask a question in this forum.

      I have problem on how to calculate a index trend based on month?

      Take the superstore sample data as the example. I can use index() to calculate the monthly rank of each city by sum of sales.

      And then I want to compare the index trends (rank change) of this city. For example. 2012-Feb, New York was #10, then on 2012-Mar, New York was #8, so the rank change is +2.

      I thought about show the index of these two period side by side, but I found that Tableau can't calculate directly on these two result.

      I also think about the lookup(), but find it is not same function as Excel....

      I stucked here.

      Hope that anyone can help me. Thanks.

      I also attache the workbook with the salesforce data sample.

        • 1. Re: How to make an index trend by month?
          Jim Wahl

          Hi Bruce,

           

          I think you're on the right track with LOOKUP().

               Rank Change 2 = LOOKUP([index], -1) - [index]

           

          As with most table calcs, the trick is setting compute using.

           

          Since [index] is a table calc, [Rank Change 2] is a nested table calc. When you add this to the view, double-check the compute using. When you open the Edit Table Calculation dialog, you'll see a pull-down menu at the top with both table calcs, [Index] and [Rank Change 2]. [Index] should be as you had it before > Advanced > Month of Date and City should be in the right-hand Compute Using box with a sort order of SUM(Sales) descending. After clicking OK, verify Restarting Every is set to Month of Date.

           

          Then, in the same dialog box, select Rank Change 2 and verify it's set to compute using Order Date (same as Table Across) and Restarting Every is set to None.

           

          Note that fields used in the table calcs---[index] and SUM([Sales])---do not need to be in the view for the table calcs to work.

           

          See attached.

           

          Jim

           

          P.S. You may also want to use a more nuanced Rank calculation that takes into account ties and nulls. Something like

          Rank =

          IF FIRST() == 0 THEN 1

          ELSEIF LOOKUP(SUM([Sales]), -1) == SUM([Sales]) THEN PREVIOUS_VALUE(1)

          ELSEIF SUM([Sales]) > 0 THEN PREVIOUS_VALUE(1) + 1

          ELSE PREVIOUS_VALUE(1)

          END

          • 2. Re: How to make an index trend by month?
            Jim Wahl

            One other minor comment. You had [index] in the level of detail shelf. This isn't necessary.

            • 3. Re: How to make an index trend by month?
              Bruce Zhang

              Thanks for your answer in such short time Jim, very appreciate. I will try it later.

              • 4. Re: How to make an index trend by month?
                Bruce Zhang

                Thanks Jim, it works very well.

                So as my understanding, since the compute using is set to Table across, so the "-1" in the lookup formula means to lookup the first value to the left, right? Similarly, if the compute using is set to Table down, then the "-1" in the lookup formula means to lookup the first value upward, right?

                And thanks a lot for your meticulous suggestion of the Rank.

                • 5. Re: How to make an index trend by month?
                  Jim Wahl

                  Yes, that's right, whatever dimension is selected in Compute Using is used for addressing. Everything else is used for partitioning.

                   

                  The table across / down / down then across, ... can be a bit confusing and problematic if the view gets rearranged, and it's usually better to specify the dimension directly. Table across in your example is the same as Order Date.

                   

                  When you have some spare time, Jonathan Drummey pulled together a good intermediate / advanced summary table calcs here: http://drawingwithnumbers.artisart.org/wiki/tableau/table-calculations/

                   

                  Jim

                  1 of 1 people found this helpful
                  • 6. Re: How to make an index trend by month?
                    Bruce Zhang

                    Great! Thank you very much.

                    • 7. Re: How to make an index trend by month?
                      Bruce Zhang

                      One further question Jim, is it possible to set the city sorted by the latest month's Index?

                      I tried set the sort on "City" using Sort-->Sort by --> Field: Sales, Aggregation: Sum

                      But it seems tableau will calculate the total sum of the sales of selecting period, that's not what I want.

                      My goal is, for example, select October, November of Order Data, and City will automatically sort by the index of November. Is that possible? Can tableau support sort by aggregation?

                      Thanks.

                      • 8. Re: How to make an index trend by month?
                        Jim Wahl

                        I'd have to think if there's a more elegant way of doing this, but you can create a new calculated field called Sort Order, which is last SUM(Sales) along Order Date. Make this discrete and add it to the Rows shelf in front of City. Then you can hide the values by unchecking Show Header in the Sort Order pill.

                         

                        By default the sort order is increasing, so I made the SUM(Sales) negative and converted nulls to 0 with ZN().

                         

                        Sort Order = ZN(-LOOKUP(SUM([Sales]), LAST()))

                         

                        After adding the value, compute using should be set to Order Date for LOOKUP / LAST to work properly.

                         

                        Jim

                        1 of 1 people found this helpful
                        • 9. Re: How to make an index trend by month?
                          Bruce Zhang

                          Thanks a lot, that's works great!