
1. Re: Level of Detail  calculate rate using measures from different records
Jamieson Christian Feb 27, 2017 11:55 AM (in response to Diogo Braga)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!

Book1_JC10.1.5.twbx 41.3 KB


2. Re: Level of Detail  calculate rate using measures from different records
Jamieson Christian Feb 27, 2017 11:59 AM (in response to Diogo Braga)1 of 1 people found this helpfulNote 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 %.

3. Re: Level of Detail  calculate rate using measures from different records
Diogo Braga Feb 27, 2017 12:13 PM (in response to Jamieson Christian)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 Feb 27, 2017 12:39 PM (in response to Diogo Braga)1 of 1 people found this helpfulDiogo,
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.

5. Re: Level of Detail  calculate rate using measures from different records
Diogo Braga Mar 2, 2017 12:30 PM (in response to Jamieson Christian)Thank you for the thorough explanation.

6. Re: Level of Detail  calculate rate using measures from different records
Diogo Braga Mar 2, 2017 12:37 PM (in response to Jamieson Christian)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:
1718 Recommit: if [Academic Year]="20172018" then [Preregistered] END
Calc 2:
1617 Attending: if [Academic Year]="20162017" then [Registration Confirmed] end
Calc 3:
Recommit Rate: 1718 Recommit/1617 Attending
This is partially working, BUT I need to divide the 1718 Recommit from the previous grade of 1617 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 Mar 2, 2017 1:05 PM (in response to Diogo Braga)Diogo,
I don't think the calculations you've come up with will work, because they are datarowlevel calculations — they are too low levelofdetail 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 hardcode 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] = '20172018' THEN [Preregistered] / CASE [Grade] WHEN '1' THEN { FIXED : SUM(IF [Academic Year] = '20162017' AND [Grade] = 'K' THEN [Registration Confirmed] END) } WHEN '2' THEN { FIXED : SUM(IF [Academic Year] = '20162017' AND [Grade] = '1' THEN [Registration Confirmed] END) } WHEN '3' THEN { FIXED : SUM(IF [Academic Year] = '20162017' AND [Grade] = '2' THEN [Registration Confirmed] END) } WHEN '4' THEN { FIXED : SUM(IF [Academic Year] = '20162017' AND [Grade] = '3' THEN [Registration Confirmed] END) } WHEN '5' THEN { FIXED : SUM(IF [Academic Year] = '20162017' AND [Grade] = '4' THEN [Registration Confirmed] END) } WHEN '6' THEN { FIXED : SUM(IF [Academic Year] = '20162017' AND [Grade] = '5' THEN [Registration Confirmed] END) } WHEN '7' THEN { FIXED : SUM(IF [Academic Year] = '20162017' AND [Grade] = '6' THEN [Registration Confirmed] END) } WHEN '8' THEN { FIXED : SUM(IF [Academic Year] = '20162017' AND [Grade] = '7' THEN [Registration Confirmed] END) } END END
Revised workbook attached (version 10.1.5).

Book1_JC10.1.5_2.twbx 41.7 KB


8. Re: Level of Detail  calculate rate using measures from different records
Diogo Braga Mar 2, 2017 1:14 PM (in response to Jamieson Christian)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 Mar 10, 2017 7:44 AM (in response to Jamieson Christian)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.

10. Re: Level of Detail  calculate rate using measures from different records
Jamieson Christian Mar 10, 2017 8:45 AM (in response to Diogo Braga)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 rowlevel calculation. Use the following, and be sure that the "Total Using" is set back to "Automatic".
[Recommit Rate (AGG LOD)]
IF MAX([Academic Year]) = '20172018' THEN SUM([Preregistered]) / SUM(CASE [Grade] WHEN '1' THEN { FIXED : SUM(IF [Academic Year] = '20162017' AND [Grade] = 'K' THEN [Registration Confirmed] END) } WHEN '2' THEN { FIXED : SUM(IF [Academic Year] = '20162017' AND [Grade] = '1' THEN [Registration Confirmed] END) } WHEN '3' THEN { FIXED : SUM(IF [Academic Year] = '20162017' AND [Grade] = '2' THEN [Registration Confirmed] END) } WHEN '4' THEN { FIXED : SUM(IF [Academic Year] = '20162017' AND [Grade] = '3' THEN [Registration Confirmed] END) } WHEN '5' THEN { FIXED : SUM(IF [Academic Year] = '20162017' AND [Grade] = '4' THEN [Registration Confirmed] END) } WHEN '6' THEN { FIXED : SUM(IF [Academic Year] = '20162017' AND [Grade] = '5' THEN [Registration Confirmed] END) } WHEN '7' THEN { FIXED : SUM(IF [Academic Year] = '20162017' AND [Grade] = '6' THEN [Registration Confirmed] END) } WHEN '8' THEN { FIXED : SUM(IF [Academic Year] = '20162017' 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).

Book1_JC10.1.5_3.twbx 45.9 KB


11. Re: Level of Detail  calculate rate using measures from different records
Diogo Braga Mar 10, 2017 12:55 PM (in response to Jamieson Christian)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...

13. Re: Level of Detail  calculate rate using measures from different records
Jamieson Christian Mar 10, 2017 2:08 PM (in response to Diogo Braga)Diogo,
You will need to attach another packaged workbook. I haven't seen the data that you're posting screenshots of.

14. Re: Level of Detail  calculate rate using measures from different records
Diogo Braga Mar 10, 2017 2:10 PM (in response to Jamieson Christian)Can I send it to you email?