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

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) • ###### 1. Re: Level of Detail - calculate rate using measures from different records

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

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

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

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
• ###### 5. Re: Level of Detail - calculate rate using measures from different records

Thank you for the thorough explanation.

• ###### 6. Re: Level of Detail - calculate rate using measures from different records

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

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] /
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

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

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

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]) /
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

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... • ###### 12. Re: Level of Detail - calculate rate using measures from different records

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. • ###### 13. Re: Level of Detail - calculate rate using measures from different records

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

Can I send it to you email?

1 2 Previous Next