7 Replies Latest reply on Jul 30, 2012 7:13 AM by kettan

    How to calculate aggregates on an aggregated text table in the same workbook?

    kettan

      What I Want To Do

      I have a what-if dashboard showing outliers as banded low/high groups based on % difference (parameter) from the median.

      This is hard to understand for the end-user.

      Therefore I want to show legends as a stacked bar showing each group banded visually with names and values.

      Seeing the low-high-other groups with names and values will make it easier to understand this visualization for the end-user.

      By the way, the purpose of the visualization is to identify low/high peaks of the workload and hereby make it easier to balance/plan the workload of workers.

       

      Technical Difficulty

      I do not know how to make calculations as aggregates of aggregates.

      My hope is low that this is possible to do directly with Tableau formulas.

      Therefore my hope is more for a work-around.

       

      My Question

      Is it possible to do calculations using an aggregated text table from the same workbook as source? If yes, how?

      Any other work-around is of course also much appreciated.

       

      Attached Workbook

      Hopefully the attached workbook makes it easier to understand what I am trying to accomplish ... and to help me too.

      I have re-created the table view, formulas, and parameters as close as possible as my original workbook (with company data).

       

      Message was edited by: Johan

      See Version 2 of the workbook in which I am getting much closer to my goal.

        • 1. Re: How to calculate aggregates on an aggregated text table in the same workbook?
          Jonathan Drummey

          Hi,

           

          I'm trying to understand the goal of the view. Is it something like this?

           

          screenshot1.jpg

           

          Do you want one bar for all month/year combinations or one bar per year?

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: How to calculate aggregates on an aggregated text table in the same workbook?
            kettan

            Hi Jonathan, thank you for helping.

             

            Yes, it is something like your example with a horizontal bar in 3 parts (blue, gray, orange).

             

            "Do you want one bar for all month/year combinations or one bar per year?"

            Answer: One bar for all month/year combinations, namely something imitating a stepped color legend.

            Visually I thought about something similar to this:

            Tableau - Fewest, Other, Most - 10 Percent.png

            Tableau - Fewest, Other, Most - 15 Percent.png

            Examples show filter for 10% and 15% drawn manually in an Excel sheet

            Example Calculations 10%

            1. Blue range: FEWEST (MIN month 132 to MEDIAN month 168 * 0.9)
            2. Gray range: OTHER (MEDIAN month 168 * 0.9 to MEDIAN month 168 * 1.1)
              • Dark gray line: MEDIAN month 168
            3. Orange range: MOST (MEDIAN month 168 * 1.1 to MAX month 223)

             

            With this calculation method the months are not applicable as borders between Fewest & Other and Other & Most.

             

            The more I think about, the more I prefer your idea

            On second thought, I think a visualization with all months similar to your example is even more useful and also easier to make.

            If so, this color legend viz should show numbers (reference lines) for:

            1. Low & High month of "Fewest"
            2. Median month
            3. Low & High month of "Most"

             

            The Goal of the Visualization

            The goal of the view (at the insurance company where I work) is to see if certain weeks/months generally (year after year) have low/high peaks of work done. My real dashboard shows claims opened (created) per week (upper viz as heat map) and month (lower viz as highlight table) and various filters.

            • Identifying low peaks helps planning, such as relocating workers to other tasks in low-peak periods
            • For high peaks it is the opposite (such as postponing other tasks with less priority)
            • 4. Re: How to calculate aggregates on an aggregated text table in the same workbook?
              kettan

              Thank you, this looks perfect. I look forward to study your solution and implement it in production.

              • 5. Re: How to calculate aggregates on an aggregated text table in the same workbook?
                kettan

                There is still some fine-tuning needed.

                 

                Need to have (partly solved):

                1a) "Lower Other" must be >= MIN

                1b) "Upper Other" must be <= MAX

                 

                Here is the result with parameter "% from median" set to 20% and 35%:

                jm option 1 20 percent_cr.png

                jm option 1 35 percent_cr.png

                The lower bar with 35% from median should have MIN 132 and MAX 223.

                 

                I think 1a and 1b are solved correctly in the attached workbook (v3).

                The "only" annoyances I have noticed are:

                 

                1) too bold numbers when 30% from median is chosen (not solved)

                2) start of lower other is blue (rather than gray) and end of higher other is orange rather than gray (not solved)

                 

                Need to have (not solved):

                2a)  Some outliers in my production data set are so big that "fewest" and "other" are hard or impossible to read. Therefore I want to "cheat" with the length of the bar to maximum MEDIAN * 2.5 (or thereabout) - but still show the true value of "Most" (MAX), such as 223 in example above.

                 

                The x axis can still be continuous since "Show Header" for all columns and rows are deselected. The cheat value (text) is (I think) limited to Mark > [Mark Label] > [Mark Label Text].

                 

                I think some "cheating" is okay, because the purpose of the "color legend bar" is to make the categories "fewest, other, and most" more understandable.

                 

                Nice to have (not solved):

                2b)  Possibly there could be a dotted white line near the end of "Most" indicating that cheat for length of bar is activated. But this dotted line is a luxury which I can be without.

                • 6. Re: How to calculate aggregates on an aggregated text table in the same workbook?
                  Joe Mako

                  As for your points under 1), see the attached, you are correct in your edit, and I just applied it to the others with similar logic. To remove the thin orange most, I used another formula to detect when this is so, and not show it.

                   

                  As for everything under point 2), I was not able to understand what you are looking for. Can you provide sample data that recreates the situation? (does not need to be real, just representative). Also a mocked up image, like an edit in MS Paint, would be helpful in understanding what you are looking for in a final result, like you did before.

                  1 of 1 people found this helpful
                  • 7. Re: How to calculate aggregates on an aggregated text table in the same workbook?
                    kettan

                    Extreme outlier added

                    I have duplicated the last row in the Tableau Excel data set approx. 200 times to create an extreme outlier.

                     

                    See new example in attached workbook (2a) (solved)

                    The attached workbook shows my "color bar legend" with "length cheat" activated.

                    This is done by modifying your formula for "index 5 and 6" in [Mark Label] and [Mark Value], and also added a condition in [Mark Label Text].

                    My corrections are only qualified guessing using the trial error method, because I do not fully understand your code, although I trust it since I can see that it works as intended.

                    Therefore I hope you will look through the code to correct and optimize it, not only for me, but also others that might use this solution.

                    I do not expect the code to be bug-free yet, although my tests so far have not provoked any error.

                     

                    The dotted line (2b) (cancelled)

                    I think the dotted line confuses more than it helps. Since I cannot think of any good way to do this, I rather cancel my wish regarding this. The length of the cheated (shortened) bar is still much longer than the rest when shortened to median * 2.2 and therefore signals that some big outlier exists. This is good enough for the purpose of the "color legend".

                     

                    Cheat length formula alternatives

                    "Cheat length" does not have to be median * 2.2.

                    Other options could be a calculation that ensures that:

                    a) "Median" is never positioned in first x % of bar, such as first 30% or

                    b) "Most" never occupies more than 60% of the bar

                    For now median * 2.2 is acceptable for the data in my production dashboard.