6 Replies Latest reply on Dec 20, 2016 8:23 AM by Gene Denny

# Difference of Two Columns as a Calculated Field

Given the attached workbook, I would like to create a third column (that will actually be used for other purposes later in the project) which is simply Column A - Column B.

Column A (2015) is a table calculation showing Percent of Total Across for the testing date of 3/31/14 and I have hidden the Not Proficient column.

Column B (2014) is a table calculation showing Percent of Total Across for the testing date of 3/30/15 and I have hidden the Not Proficient column.

The third column currently shows the Sum of the Columns A and B by using the Show Row Grand Totals command.  For the Downtown row, I'd like this third column to be 44.40% - 43.60% or 0.80%.  However, I need this to actually be a calculated field that I drag to the Columns shelf.

So, I guess my real question is, "How do I create a calculated field that shows the difference of the two columns?"

• ###### 1. Re: Difference of Two Columns as a Calculated Field

Gene!!

That would be:

SUM([Number of Records]) / TOTAL(SUM([Number of Records])) - lookup(SUM([Number of Records]) / TOTAL(SUM([Number of Records])),1)

But because this is a table calc, formatting it or positioning it to where you want might be troublesome:

• ###### 2. Re: Difference of Two Columns as a Calculated Field

And how did I know that Pooja would be the first to jump on this one!

Thanks, friend!  That answers my question.  I'll work on the formatting now!

• ###### 3. Re: Difference of Two Columns as a Calculated Field

Hi Gene,

Can I ask if you managed to format the above so that the variance sat on the same row as the 2015 and 2016 data?

Thanks

Nicola

• ###### 4. Re: Difference of Two Columns as a Calculated Field

Hi, Nicola!

I ended up with this.

% Prof Current Year and % Prof Previous Year are both table calculations.  From there, Pooja helped me construct a calculated field IA Assessment Trend which computed the difference.  That calculated field was defined as:

(SUM([# Prof CY]) / SUM([# Students Current Year])) - (SUM([# Prof PY]) / SUM([# Students Previous Year]))

Basically, it allowed me to compute my percents on each side of the minus sign and then compute.

Does that help?

• ###### 5. Re: Difference of Two Columns as a Calculated Field

So in the 'previous correct' answer from Pooja, did you put calculation 2 onto the rows field?

I have a similar formula for mine, but have the same issue as Pooja's original version as I can't find a way to get the variance in line with the rest of the data.

Thanks

Nicola

• ###### 6. Re: Difference of Two Columns as a Calculated Field

I went a little different route from the original question to make it meet new local requirements, but Pooja's calculated field is what allowed me to get to that point.  Here's a little bigger screenshot of the layout I used.  Hopefully this will give you what you need, because I'M HEADED TO THE BAHAMAS!