First, Last, Again

I have a Matrix that always displays two rows of data.
One row show values from Jan 1 of the current year.
The second row shows values for today.
In the foot I show the % the values have changed so YTD with the following
formula:
=(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
First(Fields!Core.Value) * 100
This works great and looks like this:
Date | Core
--
Jan 1 2005 | $400
Aug 31 2005 | $500
--
Footer +25%
I have been asked to add in the value from a year ago today but still
display the change in value from just Jan 1.
Date | Core
--
Aug 31 2004 | $300
Jan 1 2005 | $400
Aug 31 2005 | $500
--
Footer +25% (Diff between Jan 1 and Aug 31 2005)
How would I calc the % change in the footer. My formula will not work as the
"First" value is a year ago today not Jan 1. Is there a "Middle" function ?
:) :)
Thoughts ?
Thanks in Advance
Pete Mitchellmaybe you could use groups to accomplish the division of the first row from
the other two. Then you could just hide the group header and footer of
group1 and hide the header of group 2. I am not sure how the first and last
will work with groups ... just a thought
> Date | Core
> --
> Aug 31 2004 | $300 ======== > group 1
> Jan 1 2005 | $400 ========> group 2
> Aug 31 2005 | $500 ========> group 2
> --
> Footer +25% (Diff between Jan 1 and Aug 31 2005)
"PeteMitchell" wrote:
> I have a Matrix that always displays two rows of data.
> One row show values from Jan 1 of the current year.
> The second row shows values for today.
> In the foot I show the % the values have changed so YTD with the following
> formula:
> =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
> First(Fields!Core.Value) * 100
> This works great and looks like this:
> Date | Core
> --
> Jan 1 2005 | $400
> Aug 31 2005 | $500
> --
> Footer +25%
> I have been asked to add in the value from a year ago today but still
> display the change in value from just Jan 1.
> Date | Core
> --
> Aug 31 2004 | $300
> Jan 1 2005 | $400
> Aug 31 2005 | $500
> --
> Footer +25% (Diff between Jan 1 and Aug 31 2005)
> How would I calc the % change in the footer. My formula will not work as the
> "First" value is a year ago today not Jan 1. Is there a "Middle" function ?
> :) :)
> Thoughts ?
> Thanks in Advance
> Pete Mitchell
>|||Thanks, good suggestion.
"MJ Taft" wrote:
> maybe you could use groups to accomplish the division of the first row from
> the other two. Then you could just hide the group header and footer of
> group1 and hide the header of group 2. I am not sure how the first and last
> will work with groups ... just a thought
> > Date | Core
> > --
> > Aug 31 2004 | $300 ======== > group 1
> > Jan 1 2005 | $400 ========> group 2
> > Aug 31 2005 | $500 ========> group 2
> > --
> > Footer +25% (Diff between Jan 1 and Aug 31 2005)
>
> "PeteMitchell" wrote:
> > I have a Matrix that always displays two rows of data.
> > One row show values from Jan 1 of the current year.
> > The second row shows values for today.
> >
> > In the foot I show the % the values have changed so YTD with the following
> > formula:
> >
> > =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
> > First(Fields!Core.Value) * 100
> >
> > This works great and looks like this:
> > Date | Core
> > --
> > Jan 1 2005 | $400
> > Aug 31 2005 | $500
> > --
> > Footer +25%
> >
> > I have been asked to add in the value from a year ago today but still
> > display the change in value from just Jan 1.
> >
> > Date | Core
> > --
> > Aug 31 2004 | $300
> > Jan 1 2005 | $400
> > Aug 31 2005 | $500
> > --
> > Footer +25% (Diff between Jan 1 and Aug 31 2005)
> >
> > How would I calc the % change in the footer. My formula will not work as the
> > "First" value is a year ago today not Jan 1. Is there a "Middle" function ?
> > :) :)
> >
> > Thoughts ?
> >
> > Thanks in Advance
> >
> > Pete Mitchell
> >