1 Reply Latest reply on Feb 12, 2019 11:54 AM by Esther Aller

    Rate calculation: numerator current, denominator current -1 Trend Line

    Diogo Braga

      The goal is to create a trend line of daily recommit rate.

       

      We were able to do it using table calculation, but this solution will not work in a trend line:

       

      https://community.tableau.com/message/875107?et=watches.email.thread#875107

       

      Here's what I stated on the previous question that was solved with a table calculation:

       

      Recommit Rate = Recommit (Clean) for a specific grade and academic year DIVIDED BY Eligible to Recommit for the previous grade and previous academic year.

       

      For example, in 2018-2019, 136 students attend 1st grade and are eligible to recommit to 2nd grade for 2019-2020. As of 01/23/2019, 81 students have recommitted, therefore the 2019-2020 recommit rate for 2nd grade is 81/136 = 60%

       

      2019-01-31_1611.png

       

      This is the trend line of recommit count, the goal is to create a trend line of recommit rate:

       

      2019-02-01_1041.png

        • 1. Re: Rate calculation: numerator current, denominator current -1 Trend Line
          Esther Aller

          Hi Diogo,

           

          Many table calculations can be rewritten to use LODs instead of table functions. Unfortunately anything that requires ordering cannot (such as look up the values for the previous year for every year in the data).

           

          The alternative if you can manually write out all of the year/grade combos. I would need to see the workbook to get the exact syntax, but a rough example would be:

           

          IF MIN([Year]) = "2019-2020"

          AND MIN([Grade]) = "8"

          THEN SUM( [clean recommit] ) / SUM( { FIXED : SUM( IF [Year] = "2018-2019" AND [Grade] = "7" THEN [eligible to recommit] END ) })

           

          ELSEIF MIN([Year]) = "2018-2019"

          AND MIN([Grade]) = "8"

          THEN SUM( [clean recommit] ) / SUM( { FIXED : SUM( IF [Year] = "2017-2019" AND [Grade] = "7" THEN [eligible to recommit] END ) } )

          ...

          END

           

           

          The FIXED statements are fixed to the data set level, which is necessary because we want to find the value of [eligible to recommit] to have a different grade and year then the grade and year in the IF clause it's included in.

           

          It would be a big calculation to write out, and you would need to update it every year (you could probably write in some future years).

           

           

          On another note, the Tableau website has an Ideas section where customers can add, comment, and vote on features they would like to see in the product. Votes are used to help prioritize new features. Our Development team also loves to see everyone's use cases in the comments, which helps them better design features that will meet the most possible needs.

           

          I found the following idea that may interest you: https://community.tableau.com/ideas/6968

           

          Hope this helps

          1 of 1 people found this helpful