1 2 Previous Next 23 Replies Latest reply on Mar 14, 2017 8:56 AM by Diogo Braga

    Level of Detail - calculate rate using measures from different records

    Diogo Braga

      I trying to calculate the recommit rate for each grade level in a school.

      Registration Confirmed are students attending for the current year, Preregistered are the students that have recommitted to return for the next academic year for the following grade level.

      I got this formula to partially work, but it's not using the previous grade level Registration Confirmed in the denominator.

       

      LOOKUP(SUM({ FIXED [Academic Year]="2017-2018", [School Code], [Grade]: SUM([Preregistered])}),0) /

      LOOKUP(SUM({ FIXED [Academic Year]="2016-2017", [School Code], [Grade]: SUM([Registration Confirmed])}),-1)

       

      2017-02-27_1404.png

        • 1. Re: Level of Detail - calculate rate using measures from different records
          Jamieson Christian

          Diogo,

           

          See attached workbook (version 10.1.5). I rewrote the formula like this:

           

          And then configured it to compute along "Table (across, then down)".

           

          This enables the -3 offset on the LOOKUP: you just back up 3 entries (when reading across, then down) to get to the previous year and previous grade from the values.

           

          Here is the result:

           

          Let me know if you have any questions!

          1 of 1 people found this helpful
          • 2. Re: Level of Detail - calculate rate using measures from different records
            Jamieson Christian

            Note that if you have more than one School Code on your view, you'll need to reconfigure the table calculation to ensure that numbers from a previous school don't end up being used in your Recommit Rate %.

            1 of 1 people found this helpful
            • 3. Re: Level of Detail - calculate rate using measures from different records
              Diogo Braga

              Thanks! I forgot Tableau made it so much easier. I don't get the -3, can you explain further? Or share a link? I plan to use the recommit rate as a stand alone in some occasions, not only in the table view provided as a example.

              • 4. Re: Level of Detail - calculate rate using measures from different records
                Jamieson Christian

                Diogo,

                 

                The -3 is possible because of how the table calculation is configured. For this view, when computing along "Table (across, then down)", the measures are arranged like this (for purposes of LOOKUP and other window functions):

                 

                Registration Confirmed:

                 

                Preregistered:

                 

                So, if you walk this path, you will observe that you must follow 3 arrows to get from "Last Year, Previous Grade" to "This Year, Current Grade".

                 

                Sneaky, no?

                 

                You mentioned that you will be using the calculation in other views. Note that table calculations are always dependent on the structure of the view, because they act on the view and not the underlying data. (Also note: LOOKUP is a table calculation.) You used an LOD Expression inside your LOOKUP in an attempt to do a table calculation that is independent of the view, but I don't think that's what actually happened; LOD Expressions populate at the row level, and so even though you used an LOD Expression, I think Tableau is resolving the LOD Expressions at the row level, then partitioning the results them according to the view dimensions before running the LOOKUP table calculations.

                 

                I'm not sure if this type of question is even answerable solely with LOD Expressions. Anytime you have to find a collection of rows (for e.g. a SUM) that need to relate to a specific row based on something other than a straight equality, it's a red flag that LOD Expressions probably won't work. (The most common example is: "For each row, show me the totals for all rows up to the date on this row." LOD Expressions won't solve that, but table calcs like RUNNING_SUM or WINDOW_SUM can.)

                 

                So… you may need to create tailored versions of the table calculation to work in different views (though in some cases, you may just need to adjust the Compute Along configuration). If you are trying to incorporate this data into a view that does not even include the requisite dimensions (Academic Year, and Grade), there are tricks to make the table calculations continue to work (by putting the missing dimensions on the Details shelf), but they get very hackish and difficult to maintain.

                 

                I hope this helps clarify the thought process.

                1 of 1 people found this helpful
                • 6. Re: Level of Detail - calculate rate using measures from different records
                  Diogo Braga

                  It helped me clarify how to approach this. Yes, I can leverage the table calculation, but I also need to show the recommit rate in different views outside of a table.

                   

                  For this reason, I am now trying this...

                   

                  Calc 1:

                   

                  17-18 Recommit: if [Academic Year]="2017-2018" then [Preregistered] END

                   

                  Calc 2:

                   

                  16-17 Attending: if [Academic Year]="2016-2017" then [Registration Confirmed] end

                   

                  Calc 3:

                   

                  Recommit Rate: 17-18 Recommit/16-17 Attending

                   

                  This is partially working, BUT I need to divide the 17-18 Recommit from the previous grade of 16-17 Attending. Give this requirement, I am uncertain how to proceed. In this case, do I need a LoD Calc?

                   

                  Let me know if I should start a new discussion for this question.

                   

                  Thanks again!

                  • 7. Re: Level of Detail - calculate rate using measures from different records
                    Jamieson Christian

                    Diogo,

                     

                    I don't think the calculations you've come up with will work, because they are data-row-level calculations — they are too low level-of-detail to be of any use for this exercise.

                     

                    If you attempt to do this without table calculations, your calculation is going to be pretty nasty, because you can't do LOD Expressions that are in any way based on the value of the current data row. You would basically have to hard-code each possible combination. For example, the following works in your current view. (If you used this in another view that doesn't include [Academic Year], you may need to encapsulate the whole thing in yet another LOD layer.)

                     

                    [Recommit Rate (LOD)]

                    IF [Academic Year] = '2017-2018' THEN
                    [Preregistered] /
                    CASE [Grade]
                    WHEN '1' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = 'K' THEN [Registration Confirmed] END) }
                    WHEN '2' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = '1' THEN [Registration Confirmed] END) }
                    WHEN '3' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = '2' THEN [Registration Confirmed] END) }
                    WHEN '4' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = '3' THEN [Registration Confirmed] END) }
                    WHEN '5' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = '4' THEN [Registration Confirmed] END) }
                    WHEN '6' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = '5' THEN [Registration Confirmed] END) }
                    WHEN '7' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = '6' THEN [Registration Confirmed] END) }
                    WHEN '8' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = '7' THEN [Registration Confirmed] END) }
                    END
                    END
                    

                     

                     

                    Revised workbook attached (version 10.1.5).

                     

                    • 8. Re: Level of Detail - calculate rate using measures from different records
                      Diogo Braga

                      Thanks again for the through response. I will try it out and keep you in the loop.

                      • 9. Re: Level of Detail - calculate rate using measures from different records
                        Diogo Braga

                        So...the calcs you recommended are working beautifully, except when I add subtotal. The calculation for the sub total is basic division by the values shown below inside the red box.

                         

                        Is it possible in Tableau to combine calculations (one for grade level and one for total) to show in a format like this?

                         

                        Let me know if I should start a new discussion.

                         

                        2017-03-10_1031.png

                        • 10. Re: Level of Detail - calculate rate using measures from different records
                          Jamieson Christian

                          Diogo,

                           

                          That's not a basic divison. 940 / 469 does not equal 419.1%

                           

                          What's happening is that by default, Tableau will SUM the values in the rows. For percentages, this almost never makes sense.

                           

                          One quick fix (which I'm only mentioning because I suspect a lot of people don't know about this feature) is to change the default "Total Using…" setting. Right click the field in the view and change "Total Using" from "Automatic" to "Average".

                           

                           

                           

                          Keep in mind, however, that this is an unweighted average. That is, it's just taking the average of the percentages in the rows above, without regard for how many students those percentages actually represent.

                           

                          To get a true weighted average, you'll need to modify the LOD Expression to function as a simple aggregation (AGG) rather than a row-level calculation. Use the following, and be sure that the "Total Using" is set back to "Automatic".

                           

                          [Recommit Rate (AGG LOD)]

                          IF MAX([Academic Year]) = '2017-2018' THEN
                          SUM([Preregistered]) /
                          SUM(CASE [Grade]
                          WHEN '1' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = 'K' THEN [Registration Confirmed] END) }
                          WHEN '2' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = '1' THEN [Registration Confirmed] END) }
                          WHEN '3' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = '2' THEN [Registration Confirmed] END) }
                          WHEN '4' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = '3' THEN [Registration Confirmed] END) }
                          WHEN '5' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = '4' THEN [Registration Confirmed] END) }
                          WHEN '6' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = '5' THEN [Registration Confirmed] END) }
                          WHEN '7' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = '6' THEN [Registration Confirmed] END) }
                          WHEN '8' THEN { FIXED : SUM(IF [Academic Year] = '2016-2017' AND [Grade] = '7' THEN [Registration Confirmed] END) }
                          END)
                          END
                          

                           

                           

                           

                          The result is a weighted average that is probably what you seek:

                           

                           

                          Attached is a revised workbook (version 10.1.5).

                          1 of 1 people found this helpful
                          • 11. Re: Level of Detail - calculate rate using measures from different records
                            Diogo Braga

                            This is great!

                             

                            I applied your recommendations step by step by I didn't get the same results though.

                             

                            1. Modified LOD Expression to function as a simple aggregation (very interesting fix)

                            2. Set 'Total Using' to 'Automatic' (thanks for sharing about this feature)

                             

                            Did I miss anything?

                             

                            My data set set has more schools, but I can't think of anything else different.

                             

                            My calculation...

                             

                            2017-03-10_1542.png

                            • 12. Re: Level of Detail - calculate rate using measures from different records
                              Diogo Braga

                              Unless I am missing something, the recommit rate that's returning now after I applied your recommendations are in the 50% range...it's unusual.

                               

                              2017-03-10_1650.png

                              • 13. Re: Level of Detail - calculate rate using measures from different records
                                Jamieson Christian

                                Diogo,

                                 

                                You will need to attach another packaged workbook. I haven't seen the data that you're posting screenshots of.

                                1 2 Previous Next