
1. Re: Rate calculation: numerator current, denominator current 1 Trend Line
Esther Aller Feb 12, 2019 11:54 AM (in response to Diogo Braga)1 of 1 people found this helpfulHi 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]) = "20192020"
AND MIN([Grade]) = "8"
THEN SUM( [clean recommit] ) / SUM( { FIXED : SUM( IF [Year] = "20182019" AND [Grade] = "7" THEN [eligible to recommit] END ) })
ELSEIF MIN([Year]) = "20182019"
AND MIN([Grade]) = "8"
THEN SUM( [clean recommit] ) / SUM( { FIXED : SUM( IF [Year] = "20172019" 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