4 Replies Latest reply on Nov 23, 2016 7:09 AM by Joe Oppelt

# Calculation using a Calculated Field on top of a Table Calculation

Hi Everyone,

I'm trying build a calculation which uses a calculated field and a table calculation. I've spent hours on trying to get this to work. When I figured out how to get this to work for a single data set by using the ATTR() function, but when I included 2 datasets (within the same data source), it broke. Below is a screenshot of before and after when I have aggregation of 1 data set and then 2 data sets as shown by the filter (data sets exist on hour 0 and 4).

With data set from day 14 hour 0:

With data sets from day 14, hour 0 and day 14, hour 4:

So the "Mean Response Rate" is a table average of the "Actual Response Rate" column. The "Actual Response Rate" is a calculation of "Actual Response" / number of records which is another value within the same record. "Index" uses these two calculations by dividing the row's "Actual Response Rate" into the "Mean Response Rate" of the current data view i.e. "Index" = "Actual Response Rate" / "Mean Response Rate".

I figured out that I can actually calculate Index when the calculated field evaulates ATTR([Actual Response Rate])/[Mean Response Rate], but when adding a 2nd data set, it breaks. Now you can see the last 2 columns are actually evaluating the ATTR() function on [Actual Response Rate] so this is what I believe is breaking this.

Anyone know if this kind of calculation is possible i.e. Calculated field is calculated using a calculated field and a table calculation?

Attached is a sample data set with the calculations described above.

• ###### 1. Re: Calculation using a Calculated Field on top of a Table Calculation

Upload a sample workbook.  Too many moving parts to guess what's going on in there.

I'd want to see how the second data set is interacting here.

• ###### 2. Re: Calculation using a Calculated Field on top of a Table Calculation

I've attached a small sample with the current calculations I have to the original post. I may have confused everyone a little bit describing a data set as different data sources, but a data sets that are within the same source. In the data source, you'll see groups of data by year, month, day, hour, and each data set has 20 rows separated by "Ranked Tile". If you slide the filters to include more data sets, you'll find "index" values become "null".

In addition, the calculation for "index" did not work for 1 data set, until I used the ATTR() function.

• ###### 3. Re: Calculation using a Calculated Field on top of a Table Calculation

I figured it out! Some of my original calculations were wrong. All my calculations had to be aggregation calculations. Which actually meant that some of the computed values I had were in fact incorrect. "Actual Response Rate" was incorrectly calculated which i.e. [Actual Response]/([Cumulative Records]/[Ranked Tile]). It should have been an aggregation calculation where "Ranked Tile" wasn't suppose to sum, but stay the same value i.e. SUM([Actual Response])/(SUM([Cumulative Records]/ATTR([Ranked Tile])) which results with the correct value. Then I can use that aggregated value in the table calculation appropriately.

• ###### 4. Re: Calculation using a Calculated Field on top of a Table Calculation

Sometimes the exercise of building the sample workbook helps us see things like this!