3 Replies Latest reply on Oct 1, 2016 5:40 PM by craig warpack

    Index/Rank not working on a Table Calc Difference

    craig warpack

      Hi all,

       

      I've been trying to get a rank/index working so that I may do a top n/bottom n filter on the difference between 2 years (for illustrative purposes).  Although I've sorted (by making the delta a dimension), the index/rank function isn't working as expected. I've tried multiple variations of addressing/portioning, to no avail.

       

      Can someone please share whether this is possible or not?

       

      Below's a screenshot and I've attached the mockup twbx for quick reference.

       

       

      Thank you.

        • 1. Re: Index/Rank not working on a Table Calc Difference
          Jonathan Drummey

          Hi Craig,

           

          Where I'm from (Maine) there's a saying "You can't get there from here." This is one of those cases, the reason why is that the difference from prior is a quick table calculation and we can't sort a dimension or a table calculation partition on the results of a table calculation.

           

          That said, we have the use of RANK functions which effectively sort the partitions based on the given measure, so in the attached workbook I did the following:


          1) Dragged the quick table calculation from Measure Values to the Measures window so it would be come a calculated field and named it Diff from Prior.

          2) Created a new calculated field Rank with the formula RANK_UNIQUE([Diff from Prior],"asc").

          3) Dragged that into the view on top of your Index calc and gave it a *nested* compute using on Sub-Category so the original Diff from Prior retains its Compute using on Table (Across).

           

          I made one other change: I unhid 2013 and used a table calculation filter. I personally try to avoid Hide because it's a manual change and when the year changes then I'd have to go into the view to fix it. Also there's no obvious evidence in the view that hiding has been done. Instead I created a filter with LAST()=0 as the formal and set the Compute Using to Order Date, this will get the latest year's data as a "late" filter so we only show the data for 2014.

           

          Here's the view:

           

          Screen Shot 2016-10-01 at 7.15.44 PM.png

           

          Workbook is attached. Let me know if you have any questions!

           

          Jonathan

          2 of 2 people found this helpful
          • 2. Re: Index/Rank not working on a Table Calc Difference
            Matt Lutton

            Happy Analyzing to All! So cool to see results like this.

            • 3. Re: Index/Rank not working on a Table Calc Difference
              craig warpack

              Excellent, thank you very much.  This is will be very helpful tool to have!  Thanks a lot for providing the example and thorough explanation - very much appreciated.

               

              Best,
              Craig