9 Replies Latest reply on Oct 1, 2013 10:20 PM by Jim Wahl

    How to I combine calculated fields

    taylor.johnson

      Hello,


      I have the following ATTR calculated fields:

       

       

      IF ATTR([Status])="2. In Progress" THEN

          sum([Loan Amt (MM)])

      End

       

      -------------

      IF ATTR([Status])="3. Lender Selected" THEN

          sum([Loan Amt (MM)])

      End

       

      How do I combine these statements?  The goal is to have 1 column that aggregates the ‘Loan Amount’ based on the particular ‘Status’.  Please see the attached picture.

        • 1. Re: How to I combine calculated fields
          Jim Wahl

          The easiest thing might be to use Tableau's Group feature to combine the In Progress and Lender Selected amounts.

           

          Ctrl-click to select both fields, right-click > Group.

           

          Now you'll have three rows and one column:

           

          StatusAmount
          In Progress & Lender Selected454
          Closed1,303
          Dead181
          • 2. Re: How to I combine calculated fields
            taylor.johnson

            Hmm I see, this is my goal...

             

            In Progress is made up of multiple loans.  Im trying to take the loans that make up In Progress and divide them by the total In Progress Amount ($210).  This will help me calculate a weighted average.

             

            Thus, I am trying to figure out how to calculate the grouped total, so I use that value in an equation.

            • 3. Re: How to I combine calculated fields
              Jim Wahl

              I'm still a little fuzzy on your goal, but you can use an OR function in your original formula

               

              Amount In Progress and Lender Selected =

              IF [Status] == 'In Progress' OR [Status] == 'Lender Selected' THEN [Amount] END

               

              This will be evaluated for every row and you can SUM() the measure to get the total.

               

              As with any other regular measure, this new field will be partitioned, or aggregated, by dimension pills in the view. If, for example, you put the Status on the Rows shelf (instead of Status (group)), you're going to get the sum for In Progress and Lender Selected individually (210 and 244, respectively). If this is a problem for you, you can workaround this with a table calc---a primary use case for table calcs is to allow different levels of aggregation in a view.

               

              Amount In Progress and Lender Selected TC =

              WINDOW_SUM(SUM(Amount In Progress and Lender Selected))

               

              When adding the table calc, right-click and set Compute Using / Addressing to Status. Now you'll get the combined 454 value for all Status rows.

               

              Jim

              1 of 1 people found this helpful
              • 4. Re: How to I combine calculated fields
                taylor.johnson

                Hey,


                Sorry for all the back and fourth, I tried your equation but it was giving me a Null error.

                 

                I have attached an updated image that should hopefully clarify.

                 

                In the column called 'Calculation' this is what I am trying to achieve.  I want to divide the amount of individuals by the value of the loans in that Status.10-1-2013 11-27-28 AM.jpg

                • 5. Re: Re: How to I combine calculated fields
                  Jim Wahl

                  You can use a quick table calculation to calculate the percent of total for each category.

                   

                  Click the SUM(Amount) pill and select Quick Table Calculation > Percent of Total. By default it will calculate the percent based on the total for all categories, but you can change this. Click on the pill again > Edit Table Calculation > Compute Using and select Property. Now the table calc will be partitioned by other dimensions in the view; in this case Status.

                   

                  You can see the underlying formula by clicking on the SUM(Amount) table calc pill and selecting Edit Table Calculation. If you don't want the 100% values to show on the sub-totals, you can add some logic

                  IF   SUM([Amount]) / TOTAL(SUM([Amount])) < 1
                  THEN SUM([Amount]) / TOTAL(SUM([Amount]))
                  END
                  

                   

                  See attached.

                   

                  2013-10-01 22-05-25.png

                  1 of 1 people found this helpful
                  • 6. Re: Re: Re: How to I combine calculated fields
                    taylor.johnson

                    Hey,

                     

                    I replicated the calculations you provided in your workbook.  However my % of total is:

                     

                    1.  Dividing by the Sum of all the groups

                    2. Or if I change the calculation to Compute Using > Property, the table does not display any values

                     

                    Can you take a look at the attached workbook?

                    • 7. Re: Re: Re: Re: How to I combine calculated fields
                      Jim Wahl

                      Hi Taylor,

                       

                      Here is your layout -- the main difference from my example above is that you have two additional dimensions, Fund to the left and Lender to the right of Property:

                      2013-10-02 06-35-38.png

                       

                      Ninety percent of the complexity of table calcs is setting the partitioning and addressing (compute using) settings.

                       

                      I sometimes feel that Tableau over complicates this by providing several methods. You can

                      1. Select a single dimension for addressing (by, for example, selecting Compute Using > Property). In this case the table calc is partitioned by all other dimensions in the view. In your example, when I select Compute Using > Property, the table calc is still being partitioned by Lender, which means the TOTAL(SUM(Amount)) table calc is summing all of the members of the Property dimension for each Lender. If you remove Lender from the view the table calc will work.
                      2. Select multiple dimensions using Table(down), Table(across), ..., settings. Table(down) is the default in your case and results in all of the dimensions on the Row shelf being used for addressing, which means that the table calc is summing the Amounts for all Status, Fund, Property, Lender.
                      3. Configure partitioning and addressing using Edit Table Calculation > Compute Using > Advanced. Tableau calls this "advanced", but I often find it the easiest to use, since you can explicitly set what dimensions are used for partitioning and what dimensions are used for addressing.

                       

                      To calculate the sum for each Status above, we want the table calc to use all of the values for Fund, Property and Lender. Click the Amount of Percent Of Total field and select Edit Table Calculation > Compute Using > Advanced. Move Fund, Property, and Lender to the Addressing side. (While it doesn't matter with a TOTAL(SUM()) aggregation, Addressing is hierarchical, so ideally you want the fields on the addressing side to be in the same order you have them in the view.)

                       

                      Another nice feature of the Edit Table Calculation dialog box is the Description of the compute using; here: Totals summarize values from Fund, Property, Lender for each Status.

                      2013-10-02 06-44-52.png

                       

                      And now, I think, you have the result you want:

                      2013-10-02 06-53-22.png

                       

                      Jim

                      • 8. Re: Re: Re: Re: How to I combine calculated fields
                        taylor.johnson

                        Jim,

                         

                        Thank you so much ... that makes a lot more sense now.  This is probably the reason why the amount of each loan is being divided by the total sum of all the status (instead of each particular one).  I'm having that issue on my regular spreadsheet that also has additional dimensions.

                         

                        I assume I would have to apply similar logic to my windowsum calculation?

                        • 9. Re: Re: Re: How to I combine calculated fields
                          Jim Wahl

                          No problem.

                           

                          Yes, you'll need to set the partitioning and addressing for all table calcs, including WINDOW_xxx() functions. Measures that include table calcs have a triangle symbol on the pill.

                           

                          Again, I usually set the partitioning / addressing explicitly, with either Compute Using > dimension or with the advanced dialog box. One small downside with this approach is that if you remove a field from the view that was used in the addressing (such as Lender above), the measure and related fields will turn red, indicating an error (all addressing fields need to be in the view).