5 Replies Latest reply on Jan 19, 2017 1:22 PM by Mikey Michaels

    Sort and filter calculated field

    Mikey Michaels

      Hello All,

      I hope this message finds you well.

      I need some help first sorting filtering my calculated field " Sign Accounts calculation". I would like to sort on this field in descending order by Payer.

      Also, I would like to only present those Payer where the " Sign Accounts calculation" > 25K. Let me know if there are any questions.

       

      As always, thank you for your time!

       

      Cheers,

      Mikey

        • 1. Re: Sort and filter calculated field
          Joe Oppelt

          Always provide your tableau version.  When I open this here, (I default to 10.1) it upgrades.  Therefore any changes I make will not be accessible to you unless you also have 10.1.  I could try a bunch of versions to see which one opens without upgrading, but that gets old... 

           

          You can take your calc field, put it on your detail shelf, change it to DISCRETE, and then drag that pill to the front of your entire list of ROW pills.  This will cause tableau to sort by that calc.  However, this will only do an ASCENDING sort.  If you want to make it descending, create a second calc that multiplies the calc value by -1.  Then do the same exercise with the new calc instead.

           

          You can drag your original calc to the filter shelf and select for values >25K.

          1 of 1 people found this helpful
          • 2. Re: Sort and filter calculated field
            Mikey Michaels

            Thank you so much, Joe!

            I followed your instructions and have a follow-up question.

             

            I noticed when I place my original calculated field on the filter shelf to filter my view to anything >25K, my numbers in >30 Days and Significant Accounts calculation column change to an incorrect amount. Any idea as to what is causing this? I filtered on one customer to illustrate my question.

             

             

            Before moving my original CF to the filter shelf

             

            After moving my original CF to the filter shelf

             

            Thanks,

            Mikey

            • 3. Re: Sort and filter calculated field
              Mikey Michaels

              OH and I'm using 9.3

              • 4. Re: Sort and filter calculated field
                Joe Oppelt

                I made an assumption that your calc was a table calc.

                 

                When you use a table calc as a filter, it only filters out what gets displayed.  When you use an aggregate calc (or any row-level field) as a filter, it filters out actual rows in the table that feeds the sheet.  And that skews your other calcs because the rows are removed before the calcs evaluate.

                 

                So here is what I did.  I created a table calc by using LOOKUP() to filter the sheet.  See [Filter Sign Account].  It has "IF" logic buried in the value being looked-up.  Sets a value of 1 or zero.  I put THAT on the filter shelf and selected for value = 1.  Now you get displayed only those rows with the proper value.  I hard-coded 25000 in there, but you could make this user-flexible and give the user a parameter to let them select the min value, and pop the parameter in place of the hard coded value.  Notice inside the LOOKUP there are two elements.  The first is what you are looking up.  The second is the offset to look at.  Sometimes we use LOOKUP to look at the PRIOR row, so we use -1 there.  I am using 0 here.  Table calcs get evaluated for each row, so I want it to evaluate the value in the current row, and set a 1 or 0 for each row, and then use that 1 or 0 as the value I am filtering on.

                 

                I also added the sort calc for you.  Instead of putting it at the front of the pills on ROWS, I placed it after [Sales Office] so that it actually sorts within each sales office.

                • 5. Re: Sort and filter calculated field
                  Mikey Michaels

                  Excellent!

                  Thank you so much for the explanation and help!