9 Replies Latest reply on Sep 5, 2017 8:59 AM by Joe Oppelt

    Can a calculation be used to fix a rank?

    Karen Massey

      Hi -

       

      I am ranking programs based on FY revenue, which can be updated month to month.  I want to filter my views to show programs with various KPI values and the financial impact they have, based on their ranking within their Groups and within the company overall.

       

      For example, if Overall is "1", then that set of programs will also show the financial impact on the company, and the ranking can also be used to filter the results (show me top 10 by ranking in the company overall, or group).  The pic below shows what I want to see, but was created by manually creating columns in my source data and setting the Group and Total company ranks.

      Capture.PNG

       

      I would like to create a dynamic way to set the rankings.  If I use a table rank formula, my filters affect the ranking and don't show me what I need.  For example, using Table calcs for Group rank and Total rank, then applying the filter, the table rankings are reset to be scoped only to the data resulting from the filter:

       

      Capture.PNG

       

      Is there a way to use a calculation, rather than a table calc to fix the rankings so they aren't affected by a filter?

        • 1. Re: Can a calculation be used to fix a rank?
          Joe Oppelt

          To be able to do ranks (relative to the overall data set) you need to have the whole data set in the underlying table.

           

          To filter out values from the viz without chopping out rows from the overall table, you can use a table calc as a filter.  This leaves the underlying table intact, but limits what part of the table gets displayed.

           

          Given your example, what measure did you base group rank and total rank on?  I'll hack up the two ranks.  What dimension would you like to filter on?  I'll hack up a table calc filter to show how to make that work.

          • 2. Re: Can a calculation be used to fix a rank?
            Joe Oppelt

            OK, I think I see what's what in your data.

             

            See attached.

             

            I have the rankings.  And you can filter out groups without changing the ranking values.

            • 3. Re: Can a calculation be used to fix a rank?
              Karen Massey

              The table calcs you created were similar to mine on the Table Calc sheet. I had also added a filter for month - when I add that to your Sheet 3, I get the scope reduction in the table data:

              Capture.PNG

              • 4. Re: Can a calculation be used to fix a rank?
                Joe Oppelt

                You added a filter that is not a table calc.

                 

                I'm not sure what you are looking to filter out with MY(date), but I have done some things to the workbook to show you how to make a table calc for that.

                 

                First of all, to filter a dimension with a table calc, the dimension has to be on the sheet, one way or another.  So for a first step, on sheet 3 I added MY(Reporting Date).  And then I made the same sort of table calc filter to filter out the months.  Edit [Filter Month].  It has some complicated-looking logic in it, but it was easy to put that in there.  I made a copy of [Filter Group], and in place of [Group] in that calc, I dragged the MY(Reporting Month) pill from the Columns Shelf into the calc.  Tableau expanded the logic behind the MY(...) structure in the calc for me.


                The key to making a table calc like this is the LOOKUP function.  the ,0 as the second argument says to display the current cell if it satisfies whatever is selected in the filter list.  On sheet 3, turn off some of the selections for this filter.  You'll see those months go away.

                 

                Go  to sheet 4.  Here I have dragged the MY pill from Columns to Detail.  We still get 12 marks in each row (if all values of [Filter Month] are selected).  And I'm sure you just want to see one per row.  (Notice that they're all the same value in each row.  This is why I don't understand why we want to bring the date into selection here, but your actual application may have a reason for it.)

                 

                Go to Sheet 5.  Notice the [index] pill on the filter shelf.  This is another table calc that counts the index value along each row.  I set this so that it only shows index=1.  Now you get just one mark per row.  Notice that the values have not changed from what we saw in the previous version of this workbook.

                 

                Go to Sheet 6.  I changed the filter for months to be single select, and hide the ALL choice.  Now we will always have just one month selected, which makes the INDEX calc moot (so I took that off the sheet.)

                 

                Now, I know you don't want your users having to interpret 201,610 to mean October, 2016.  We can make a text calc to do that interpretation for them if needed.

                 

                The real point of all this, though, is that if you want to filter stuff off the viz, but still keep your ranks and values intact, then you have to use a table calc to filter, not a standard quick filter.

                2 of 2 people found this helpful
                • 5. Re: Can a calculation be used to fix a rank?
                  Karen Massey

                  Thanks for the master class on table calcs!  I don't know that this is the best approach for my sheets, though, since my users will want to filter across months or other dimensions, and I don't know that creating table calcs for so many options is the way to go. For example, I can see the filtering being by month, by group, by KPI value (in my real data, I have 10 KPIs), by business unit within group (not in the sample, for brevity's sake).

                   

                  Is it possible to create a calculated field that will assign the value of rank to the program in a way that makes it static regardless of the other filtering?  e.g., something like the LOD FIXED formula?  The rank would be based on the program's FY Rev in a single month, within the org overall, and within the Group.

                   

                  I'm trying to avoid having to manually add rank values to my source data each month. :/

                  • 6. Re: Can a calculation be used to fix a rank?
                    Joe Oppelt

                    RANK has to be a table calc.  RANK s a table calc.  If you want that "hard-coded" into the data, you'll have to do that outside tableau in the data before reading it in.

                    • 7. Re: Can a calculation be used to fix a rank?
                      Kenneth Buker

                      Am on tab 10.2 but states this example could not be opened since it was created in a newer version?

                       

                      Is there an example you can show for 10.2?

                      • 8. Re: Can a calculation be used to fix a rank?
                        Karen Massey

                        Sorry, I can't save as a previous version.  Easiest thing to do is upgrade to the current version, if you can.

                        • 9. Re: Can a calculation be used to fix a rank?
                          Joe Oppelt

                          Kenneth -- You can install 10.3 Desktop on the same computer you have 10.2.  (I have individual versions from 8.2 all the way up to 10.3 so that I can open people's examples from any older version.)

                           

                          It's asking a lot to ask someone to redo their work on an older version when you have easy recourse to open the newer one.