8 Replies Latest reply on May 17, 2013 2:16 AM by Leticia Soroa

    Weight Average Calculation

    Leticia Soroa

      Hi,

       

      I wonder if someone can help me with this. I am trying to calculate the weighted average in Tableau.

       

      The data does not come clean so I have to do a couple of things

       

      Firstly I am calculating the minimun ExpiryDate to know the weight I have to apply ( Q1 - 0.25, Q2 - 0.5, Q3 - 0.75 and Q4 - 1) and the Year difference respect this min Expirty Date ( Same Year - 0, 1 year difference - 1, 2 year.. 2,.... ) and the weight will be Qweight + YWeight.

       

      After that I can multiply the JTZ by the weightapplied and I get a value for each row. However now I have to group by Name and do the

      sum (JTZ*weightApplied) / SUM(JTZ). Is this possible?

       

      I am attaching my test workbook.

       

      If you could help it would be great!!

       

      Thanks a lot!

        • 1. Re: Weight Average Calculation
          Jim Wahl

          Hi Leticia,

           

          How about using WINDOW_SUM?

               WINDOW_SUM([JTZ*WeightApplied])/WINDOW_SUM(SUM([JumpToZero$]))

           

          If you select Edit Table Calculation for this pill, you'll see it's a nested table calc. with a drop-down menu at the top of the dialog box. For this new field, select Advanced compute using and set the Addressing fields to Year of Expiry, Quarter of Expiry. Then it should sum everything in the JTZ columns.

           

          Your final layout / view isn't exactly clear to me, but perhaps Compute Using for MinExpiryDate could be set with the same addressing (you also have riskdate in the addressing column, which I'm not sure you need)?

           

          Jim

          1 of 1 people found this helpful
          • 2. Re: Weight Average Calculation
            Leticia Soroa

            Hi Jim,

             

            That is great!!! the value seems to be correct.

             

            I still have a couple of questions though

             

            • Where do you see the nested table calc? I don't seem to find it.
            • The data is now correct but let's say I want to have issuer5YearSpread and The JTZ_WeightedAverage displayed on a graph over a period of time( expiry date) is that possible??.

             

            Regarding RiskDate - I use this field to be able to know my minimum expiry date for the day I am looking at.

             

            Thanks again!

            • 3. Re: Weight Average Calculation
              Jim Wahl

              Where do you see the nested table calc? I don't seem to find it.

              Right-click on the new calculated field: JTZ_Weighted Average > Edit Table Calculation. At the top of the Table Calculation dialog box, you'll see a pull-down next to Calculated Field.

              2013-05-16 17-23-50.png

               

              The data is now correct but let's say I want to have issuer5YearSpread and The JTZ_WeightedAverage displayed on a graph over a period of time( expiry date) is that possible??.

              I don't quite understand the data or the exact view you're after, but I don't see why you couldn't have both measures on the same chart (with dual-axis). For example, you could take Sheet 2 in the example I posted and move Year from the detail shelf to Columns, move JTZ_WeighedAvg from Measures to Rows, and move Name from Rows to Colors. And you should have a line plot of this value. And now you can add another measure, such as Issuer5YearSpread, to the Rows shelf. This will give you two graphs, which can be consolidated by selecting dual-axis.

               

              Also, I don't think this is what you want, but you can change the partitioning of JTZ_WeightedAverage so that it's computed every year rather than covering all years by editing the table calc and either 1) moving Year back to the left-hand side for partitioning or 2) set "reset every" to Year. The only difference is that (2) allows you to maintain a sort order, but since your table calcs are not dependent on sort order, (1) and (2) should be equivalent. ...

               

              Jim

              • 4. Re: Weight Average Calculation
                Leticia Soroa

                Sorry Jim! completely my mistake! I hadn't seen the sheet 2!!

                 

                It is great!! thanks a lot! I need to go through it but this is what I was looking for.

                 

                Thanks again!! and sorry for not seeing all the workbook!

                 

                • 5. Re: Weight Average Calculation
                  Jim Wahl

                  No problem. Glad to help. ...

                   

                  Jim

                  • 6. Re: Weight Average Calculation
                    Leticia Soroa

                    Hi Jim,

                     

                    One last question If you look at the minimum expiry date in the TEST tab the value does not change when you change risk date. Is there any way to do this?

                    It should really do MIN expiry date for that specific RiskDate.

                     

                    Thanks!

                    • 7. Re: Weight Average Calculation
                      Jim Wahl

                      It should really do MIN expiry date for that specific RiskDate.

                       

                      To do this you need to partition by RiskDate in the table calc.

                       

                      Currently you have the MinExpiryDate pill setup with advanced compute using. So you’d go into the table calc dialog box and move risk date from the addressing side to the partitioning side. You can also choose the option reset every RiskDate. The main reason for the latter is that RiskDate can then be used to sort. In your case, WINDOW_MIN() doesn’t require a sort order, so the result is the same.

                       

                      Now your table calc is addressing only on ExpiryDate, partitioned by everything else, which in this case in Name and RiskDate. You might notice that you don’t really need to use the advanced compute using option. You can simply select ExpiryDate in the table calc pill to get the same result. This makes it easier to double-check the table calcs in the view.

                       

                      This fixes your MinExpiryDate issue, but there’s still a problem with YearWeight calculation. If you set riskdate to 2012-02-02, the MinExpiryDate is 2012-02-15 and, therefore, YearWeight should be 0, but it’s 2. What’s happening is that you want YearWeight to be partitioned on MinExpiryDate, but you can’t partition on a table calc field. I fixed this by including the formula for MinExpiryDate in YearWeight:

                           DATEDIFF( "year", WINDOW_MIN(MIN([ExpiryDate])), ATTR([ExpiryDate]))

                       

                      I also changed the last part of the formula from a MIN() to ATTR(), since you’re computing this value at the level of detail where there should only be one ExpiryDate value. In this case, MIN(), MAX(), and ATTR() will all result in the same value, but ATTR() has the advantage of returning * if there are multiple values and this provides some level of error catching.

                       

                      Check out the attached. The new calculations have the (new) suffix. Again, I'm not entirely sure I understand your data set and final display goals, so perhaps some of the above changes still need a bit of work.

                       

                      Jim

                      1 of 1 people found this helpful
                      • 8. Re: Weight Average Calculation
                        Leticia Soroa

                        Hi Jim,

                         

                        This is extremely useful stuff!!

                         

                        Thanks a lot for your help on this!

                         

                        Leticia