5 Replies Latest reply on Oct 25, 2016 1:19 PM by Benjamin Greene

    Help with stacked bars

    Matt Hong

      My Pivot Field Names include two fields, one with individuals' current monthly bills, and the other with their predicted, future monthly bills.

       

      I need to calculate the difference between these two integers, and subsequently categorize the changes into "Higher bill", "Lower bill", or "No change."  (Bonus: if either of the two fields is null, categorize the difference as "Don't Know")

       

      Subsequently, I need the % of total for each category of change, in order to create a 100% stacked bar chart. There are definitely people in each of these categories of change. There are 5 of these stacked bars corresponding to 5 groups of people responding.

       

      I am very stuck - for some reason, I can't work with the category field I have calculated (called 'Change direction'). I have attached a workbook, if someone could help me out. And could someone explain why this is so unintuitive?

       

      What I am approximately going for:

        • 1. Re: Help with stacked bars
          Benjamin Greene

          The problem you were having was because of how your data was formatted. Since the Future monthly bill values for a certain person were not in the same row in the raw data as that person's Current monthly bill value, simply subtracting one from another would not work.

           

          To remedy this, I edited both of the above calculated fields to be Level of Detail expressions (LODs) so that we can compare a person's Current monthly bill with their Future monthly bill. Finally, I edited the Change direction calculated field so that it takes into account that "Don't Know" case you mentioned. Finally, I built the view. Let me know if you are having any trouble replicating this.

          1 of 1 people found this helpful
          • 2. Re: Help with stacked bars
            Michel Caissie

            Matt,

             

            If you check in the attached,  on the sheet validate data.

            You can see that rows having a Current Monthly have a null for Future monthly  and vice versa. So the Change always returns a null .

            So what you want is for every Person to have the Current monthly and Future monthly values on every row. This can be done using lod calculations

            like this;

             

            {FIXED [Group],[Person]:SUM( if [Pivot Field Names] == 'Q23' then [Pivot Field Values] end )}

            Here for every row, you get the Q23 value of the group   Group-Person

             

            Do the same for the Q38 and now you have values on every row to compute the Change direction.

             

            Also you don't need to int the Pivot Field Values since it's already an int.

            And finally,  since you can have more than one record for each group-person-pivotField, and depending on your business logic, you may have to change the SUM aggregation in the above calculations for whatever you need.

             

            Michel

            1 of 1 people found this helpful
            • 3. Re: Help with stacked bars
              Matt Hong

              I figured that was the problem, but couldn't get my head wrapped around how to address it.

               

              Why does the LOD fix it? Seems like a very easy solution, but what is the difference in this case between the normal agg. and the LOD agg.?

              • 4. Re: Help with stacked bars
                Michel Caissie

                Well  I guessed, based on your Sheet 52, that you want to compute this for every Person-Group.

                 

                Aggregations are based on the level of detail of the view.

                And since you don't have Person in the Detail of the View it would not be possible the get the right numbers.

                Using LOD, you can fix the level of detail of the calculation directly in the calculation.

                1 of 1 people found this helpful
                • 5. Re: Help with stacked bars
                  Benjamin Greene

                  To understand the difference, let's look at 3 different, but similar ways to specify a simple subtraction.

                   

                  1. X - Y

                   

                  With calculations like this (no aggregation functions included in the formula) Tableau will go into the raw data and look directly at the value for X and compare it to the value for Y in that very same row. This is no different than making a new column in excel with a formula of =A1-B1, as Tableau will write a value for every single row in your raw data. This is how you had the formula written originally, and since there were no rows where both X and Y were present, you got all null values.

                   

                  2. SUM(X)-SUM(Y)

                   

                  With calculations like this (normal aggregation formulas), Tableau will sum all of the X values within whatever dimensions you have specified in your visualization, and then sum all the Y values within those dimensions, then take the difference. This works to compare a person's X value and Y value, even if those values are not in the same row in the raw data. However, the drawback is that you would need to make sure that a Person dimension was dragged into the view, so that Tableau knows at which level to perform the SUM. In your case, your dimension was Group, so Tableau would have calculated this difference at the Group level, rather than the Person level.

                   

                  3. {FIXED Person : SUM(X)}-{FIXED Person : SUM(Y)}

                   

                  LODs like this are powerful, because they allow you to aggregate measures at whatever granularity you desire, regardless of the dimensions in your visualization. Even better, the values they return are treated as un-aggregated numbers that can be further summed or averaged. Also, as Michel pointed out, Tableau essentially "writes" the same value to every single row in the raw data within the dimensions you specified. So, for instance, this calculation will sum all the X values for a given person, then write that number to every one of that person's rows in the raw data. It will do the same for each person's Y values, so even if a person does not have his raw X and Y values in the same row, this calculation will allow you to subtract them.

                   

                  I hope this makes sense. The biggest hill to get over is understanding how Tableau thinks and where it performs calculations and then writes the values to. Once you wrap your head around all that, everything really starts to open up for you.

                  2 of 2 people found this helpful