3 Replies Latest reply on Dec 10, 2018 6:22 AM by Paul Wachtler

    Sort all vizes on a field from one of the vizes

    Kurt Heisler
    Kurt Heisler Dec 8, 2018 9:42 AM

      I combined vizes from 3 sheets to create this dashboard:

       

      The underlying data has incarceration rates for 9 cities (City - Column E), for 8 quarters (Quarter - Column F). The most recent quarter is flagged as the current year (CurrentYr = Yes - Column J), and the quarter from one year ago is flagged as the previous year (PreviousYr = Yes - Column K).

       

      I want all 3 vizes to be sorted based on the city's incarceration rate for the current year. That's the 2nd / middle viz in the picture which is filtered on CurrentYr = Yes.

       

      How I can do that?

       

      The 1st viz is filtered on PreviousYr = "Yes".

      The 2nd viz is filtered on CurrentYr = "Yes".

      The 3rd viz is filtered on YearStatus (Column L) = "Current Year" and "Previous Year", calculates the % change in performance between these two years, and hides the column showing the Previous Year.

       

      It seems I need to include in the 1st and 3rd vizes a (calculated?) field that holds the sort order of the cities based on the incarceration rates for the current year. Any suggestions on how to do that?

       

      Workbook attached.

      • 60 Views
      • Categories: Dates & Times, Formatting & Display
      • Tags:
        • 1. Re: Sort all vizes on a field from one of the vizes
          Paul Wachtler
          Paul Wachtler Dec 8, 2018 12:24 PM (in response to Kurt Heisler)

          Hi Kurt,

           

          You can get the sort you want with a Level of Detail (LOD) calculation.  FIXED LODs are processed before dimension filters, so that's how you can create a calculation that ignores your date filters for the other two vizzes.

           

          I created this field (I called it Current Year Incarceration, but you can call it whatever you'd like) for calculating the current year's incarceration rate.  It's fixed at the city level so it disregards the dimension filters that you have on your worksheets.

           

          {fixed [City]: sum(if [Current Yr] = "Yes" then [Performance] end)}

           

          I then placed that on the detail shelf on both the LastYr and PercentChange vizzes, and sorted the City field by [Current Year Incarceration} descending.  That gives you what you want.

           

          Screen Shot 2018-12-08 at 3.19.07 PM.png

           

          I updated the attached .twbx file with this solution as well.  Let me know if you have any questions.

           

          Best,

          Paul

          • 2. Re: Sort all vizes on a field from one of the vizes
            Kurt Heisler
            Kurt Heisler Dec 9, 2018 7:38 PM (in response to Paul Wachtler)

            In an effort to simplify the data example I omitted some important information.

             

            Each city is actually repeated in the data set many times, once for value of Headline, Indicator, Storyline, and Storyline Values (i.e., the filters for each viz).

             

            For example, the full data file also shows performance for each city for:

             

            - different values of StorylineValues, e.g., the incarceration rate for people age "18-30," "31 - 50," etc. In my sample file I included only the "Total" Storyline value (which is all ages).

            - different values of indicator, e.g., the "graduation rate," the "% of violent robberies," etc.

             

            So, fixing the field only to "fixed [City]" won't work, because it will sum the performance for all instances of the City in the data, instead of only those instances where Headline = Yes, Indicator = Incarceration rate, Storyline = Age at incarceration, and Storyline Values = Total. Can you fix on multiple dimensions a LOD expression?

            • 3. Re: Sort all vizes on a field from one of the vizes
              Paul Wachtler
              Paul Wachtler Dec 10, 2018 6:22 AM (in response to Kurt Heisler)

              Hi Kurt - yes you can add as many dimensions to the beginning part of the LOD expression as you'd like.  Sounds like you need something like this:

               

              {fixed [City], [Headline], [Indicator], [Storyline]: sum(if [Current Yr] = "Yes" then [Performance] end)}

               

              Here's more information on LOD expresssions too:

              Overview: Level of Detail Expressions - Tableau

               

              Best,

              Paul