
1. Re: Using Window_Sum on a Table Calculation (Difference from Previous Year)
Brad Llewellyn Apr 5, 2013 12:26 PM (in response to colby.pash)Colby,
Difference from Previous Year is typically calculated as
SUM( [Sales] )  LOOKUP( SUM( [Sales] ), 1 )
with Compute Using: [Year] or YEAR( [Date] )
If you need help understanding Table Calculations, especially Compute Using, I have two blog posts about them.
http://breakingbi.blogspot.com/2013/03/workingwithwindowcalculationsand.html
http://breakingbi.blogspot.com/2013/02/workingwithtablecalculationsin.html
Depending on you chart, this may be more complex.
Let me know if this helps,
Brad Llewellyn
Associate Consultant
Mariner, LLC
brad.llewellyn@marinerusa.com

2. Re: Using Window_Sum on a Table Calculation (Difference from Previous Year)
colby.pash Apr 5, 2013 12:41 PM (in response to Brad Llewellyn)Thanks Brad. I've been going about about this and I've tried several calcuations. The calc below isn't giving me what I want, but I don't think it's far off.
WINDOW_SUM(ZN(SUM([Revenue]))  LOOKUP(ZN(SUM([Revenue ])), 1),FIRST(),FIRST()+19)
Compute along Previous Year.

3. Re: Using Window_Sum on a Table Calculation (Difference from Previous Year)
colby.pash Apr 5, 2013 12:39 PM (in response to colby.pash)Then I'm also looking at doing:
WINDOW_SUM(ZN(SUM([Revenue]))  LOOKUP(ZN(SUM([Revenue])), 1), LAST()19,LAST())
as well, and eventually this one to find the Net of the Top and Bottom performers
(WINDOW_SUM(ZN(SUM([Revenue]))  LOOKUP(ZN(SUM([Revenue])), 1), LAST()19,LAST()))
+
(WINDOW_SUM(ZN(SUM([Revenue]))  LOOKUP(ZN(SUM([Revenue])), 1), FIRST(), FIRST()+19))

4. Re: Using Window_Sum on a Table Calculation (Difference from Previous Year)
Brad Llewellyn Apr 5, 2013 1:34 PM (in response to colby.pash)Colby,
Syntactically, each of these ideas seems plausible. Whether or not it gives you the correct answer will depend greatly on how your data and chart are structured. Could you post a mockup or .twbx file?
Thanks,
Brad Llewellyn
Associate Consultant
Mariner, LLC
brad.llewellyn@marinerusa.com