3 Replies Latest reply on Apr 22, 2013 6:01 PM by Parker Schlank

# Simple YOY Calculation

Hello!

Please excuse my question if it's overly simple.  I've been dragging & dropping for the better part of an hour without any progress.

I'm trying to add two simple calculations to my Sheet 1.  I would like them to appear where the two black boxes are as #1 & #2 respectively.

#1 - a column that calculates the difference between [1Q2012;Total] & [1Q2013;Total] as an integer.  (The answer in this example case would be 17,813)

#2 - a second column that calculates the difference between [1Q2012;Total] & [1Q2013;Total] as an percentage.  (The answer in this example case would be +16.7%)

I would like them to be dynamic, in that if I were to drop something onto the row filter (say client name) that newly added columns #1 & #2 would update accordingly.

If anyone can aid in how I might go about setting this up, I would be very appreciative!

- Parker

• ###### 1. Re: Simple YOY Calculation

Hi Parker,

These values can be found. However, they won't be able to be placed in the desired place on a worksheet. Each value can be found on separate sheets and then placed on a dashboard together in the desired view.

To get #1, create a calculated field similar to the following:

(ZN(SUM([TrxCount])) - LOOKUP(ZN(SUM([TrxCount])), -1))

To get #2, the calculation should look like the following:

(ZN(SUM([TrxCount])) - LOOKUP(ZN(SUM([TrxCount])), -1)) / ABS(LOOKUP(ZN(SUM([TrxCount])), -1))

Hope this helps!

-Tracy

1 of 1 people found this helpful
• ###### 2. Re: Simple YOY Calculation

[Tracy]

This calculation is very helpful.  Thanks for passing along.

I added the calculation and changed [Calculation1] to [Compute Using: Advanced], [Addressing: Year of Date].  This gave me YOY growth numbers I was trying to compute.

I then added [Calculation1] to the Measure Values, but of course I wanted to see it as a 'column' not a row:

So I then grabbed on the row box icon:

and dragged the icon up to the column:

This gave me nearly the result I was looking for, with the only exception being that 2012 shows up on the display & is 'blank'

Would it be possible to write additional syntax in the formula to isolate / hide the blank 2012 values (above in red)?  If so, I would call this close enough for the report I'm trying to produce.

Thanks again for any insight!

- Parker

• ###### 3. Re: Simple YOY Calculation

[All]

Figured it out.  Needed multiple calculated fields:

[1Q12] = IIF(YEAR([Date]) = 2012, IIF(MONTH([Date]) < 4, [TrxCount], 0), 0)

[1Q13] = IIF(YEAR([Date]) = 2013, IIF(MONTH([Date]) < 4, [TrxCount], 0), 0)

[B(W) #] = SUM([1Q13]) - SUM([1Q12])

[B(W) %] = IIF(SUM([1Q12]) = 0, 1, (SUM([1Q13]) - SUM([1Q12]))/SUM([1Q12]))

Piece of cake actually.

Example workbook attached.  Hopefully this helps someone else that was looking for this information!

- Parker