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.
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:
Workbook is attached. Let me know if you have any questions!
Jonathan
