2 Replies Latest reply on Apr 20, 2012 1:41 PM by Jonathan Drummey

    create expand/collapse for selected categories (like top n)

    Mark Holtz

      I have a workbook where I have data for several radiologist clients.

      Each client could have a number of different categories of the type of procedures they perform -- let's say about 10 or 12 each. (with thousands of records)

       

      For example, Client1 could have Diagnostic, Ultrasound, MRI, etc. while Client2 could have Ultrasound, PET, Mammography, etc.

      I want to present a parameter that allows the user to see a "full" view or a "collapsed" view of all the procedure types that are associate with a client. (They always view one client at a time--client is set as a context filter.)

       

      I want the "full" view to show the regular list of ALL 10 or 12 procedures, sorted by descending volume.

      The tricky part is that when the user selects the "collapse" option, I want to create groups around the different dimension values so that I show all the procedure types that have more than 2% of the total, and then lump all procedure types that have less than 2% each into a bucket of "Other."

       

      So I might see just 6 major categories for Client1 and then the "Other" would hold the rest.

      For Client2, I might have 10 categories, each of which has at least 2% of the overall volume--then just a couple (or none) in the Other...

       

      I can get the 2% with this formula:        sum([Number of Records]) / total(sum([Number of Records]))

      I tried making an if statement as:  IIF(sum([Number of Records])/total(sum([Number of Records]))< .02 , 'Other' , attr([ProcedureGroup]))

      But then if I try to display the formula by itself, I just get the * instead of the breakout of each Procedure type...

       

      I've slapped together an example and included it.  I replaced Procedure Types with just letters, but hopefully you get the idea.

      Sheet 1 shows what I got to work and sheet 2 shows the problem (when removing the 1st column) .

      Ultimately, I want to use this on a bar chart across dates. (I want the calculations to be at the procedure level, I don't care about which dates had the volume)

        • 1. Re: create expand/collapse for selected categories (like top n)
          Jonathan Drummey

          Hi Mark,

           

          I'm working on a solution, I'm most of the way there, I just need to figure out how to deal with the situation where the sum of the Other is bigger than one or more of the Procedure Types. If I can't get that by sometime later tomorrow I'll post what I've got.

          • 2. Re: create expand/collapse for selected categories (like top n)
            Jonathan Drummey

            Hi Mark,

             

            Here you go.

             

            Given that the goal is to expand or collapse a set of rows based on a measure (% of Total) that is already a table calculation, it gets a mite sticky. You'd created the Procedure Type (2%) field that assigned all the ones with less than 2% of the total to the Other group, but now we need to generate a total of the % of Totals of all Procedure Types in the Other group. Ideally we'd be able to just have another table calculation that sums up the % of Total and partitions on the Procedure Type (2%) field, but Tableau does not support partitioning by table calcs. In addition, Tableau can't sort a discrete table calculation result other than manually, and we need a dynamic sort.

                            

            Over the last few weeks I've been running into multiple situations that would have been made possible of tremendously easier if Tableau partitioned by table calcs, and this problem seemed like one that was simple enough that I could find a workaround. So, here's what I did, with some trial and error:

             

            - Created the Day of Procedure Date calc that is is DATETRUNC('day',[ProcedureDate]). This simplifies how we look at the dates in table calcs.

             

             

            - Created the % to Hide Parameter to make the number of procedures that are grouped into the Other bucket configurable.

             

            - Adjusted the ProcedureType (2%) calc to respect the Expanded (show all values of Procedure Type) or Collapsed view, and set it to use the True % of Total calc below, I called the new version ProcedureType (2%) (revised)

             

            - Put ProcedureType on the Level of Detail, with sort set to Descending/Number of Records/Sum.

             

            - Created the True % of Total field that is SUM([Number of Records])/TOTAL(SUM([Number of Records])). That's pretty straightforward.

             

            - Created the Index to 1st Other calc:

             

             

             

            IF [Procedure Type (2%) (revised)] = "Other" AND LOOKUP([Procedure Type (2%) (revised)],-1) != "Other" THEN

                //return the index of the first row in Other

                INDEX()

            ELSEIF [Procedure Type (2%) (revised)] = "Other" THEN //get the value of INDEX() found above

                PREVIOUS_VALUE(0)

            ELSE

                //any row that is not collapsed

                0

            END

             

            This identifies which row in the results goes with the start of the Other group that is created by the ProcedureType (2%) calc.

             

             

             

            - Created the Last calc:

             

            LAST()

             

             

            This is due to some issue I don't quite understand with how Tableau was sorting the data when I included the dates in the view, having this available as an independent field fixed the problem.

             

             

             

            - Created the % of Total calc:

             

            IF [Expanded/Collapsed] = "Expanded" THEN

                [True % of Total]

            ELSE //view is collapsed

                IF [Index to 1st Other] = 0 THEN //we're in one of the non-collapsed rows

                    [True % of Total]

                ELSEIF [Index to 1st Other] = INDEX() THEN // this is the first row of the Other group

                    WINDOW_SUM(SUM([Number of Records]),-(SIZE()-[Index to 1st Other])+[Last], [Last])/TOTAL(SUM([Number of Records]))

                END //we're not performing the calculation for any other rows in the Other group,

                    // or else we'll get overlapping rows

            END

             

             

            This calculation returns the True % of Total for all rows in the Expanded view, when Collapsed it returns the True % of Total for rows that aren't in the Other group, and then for Other returns the % of total for all rows in Other.

             

             

             

            The Compute using for % of Total and its nested calcs is set to Advanced.. with Compute using ProductType and Sort on Number of Records/Sum/Descending. Now, this works great, except that the view won't properly sort without a little help. When it comes to sorting table calculations that are discrete (blue) pills, the only options are a manual sort or the data source order, the latter is numeric or alphabetical ascending.

             

             

             

            - Therefore, we can create a calculation that returns a numeric number that Tableau will sort by, put that as the left-most discrete (blue) pill on the Rows shelf, and then uncheck Show Header for that pill so it is hidden and provides an invisible sort. Here's the Sort calculation:

             

             

            IF [Expanded/Collapsed] = "Expanded" Or [Index to 1st Other] = 0 THEN

                -SUM([Number of Records])

            ELSE

                0

            END

             

            That works for the basic view shown in 1. working with no dates.

             

            When dates come into the view, the sorting changes so we have to set up the table calculations correctly. Every single table calculation (including all the nesting for the % of Total calc) needs to be set to: Compute Using: Advanced, with Day of Procedure Date above ProcedureType, sorted on Number of Records/Sum/Descending, restart every Day of Procedure Date. This way the procedures for each day will be sorted for that day. Anything else causes weird things to happen, like the Other total being incorrect.

             

            A working view of the dates can be seen in 2. working with dates.

             

            Now we can create the bar chart showing % of total by day, that's in 3. working bar chart.

             

            Here's a simplified review of the process, for my sake as much as anyone else's:

             

            1. Create a table calc measure, like % of Total that you want to view across a generated set of partition(s).

            2. Create a table calc that generates the partition(s), that's the Procedure Type (2%) (revised) calc here.

            3. Flag the starting row in partition of interest, that's the Index to 1st Other calc.

            4. Create the displayed version of the measure that shows the measure from step 1 (% of Total) for the rows not in the partition of interest, and a custom version of the measure that only operates within the partition of interest, that's the Index to 1st Other calc.

            5. If necessary, set up a measure that will be used as a hidden header to sort the results.

             

            Let me know if this works for you!

             

            Jonathan