Hi,
For columns in a matrix, I need to add the row value to the row value in
previous column. Is there a way to do this?
Here is what I want the matrix results to look like:
year1 year2 year3 year4
Amount 1 3 4 7
Total 1 4 8 15
Year is the column group value and amount is the row group value. Total is
the field I am looking for how to calculate.
Thanks!Initially I thought you were after a running total, but looking at your
example that doesn't appear to be the case.
You will have to do this in the underlying query by including the two
consecutive year's values on the same row; i.e.
Select A.Year, Sum(A.Value), (Select Sum(B.Value) From MyTable B Where
B.Year = A.Year-1)
From MyTable A
Group By A.Year
(I'm not sure this is syntactically correct, but you get my drift)
Having said that, mathematically what you're showing would appear
incorrect, effectively the year2 total is included in the year4 total
twice. What are you trying to show?
Chris
Me wrote:
> Hi,
> For columns in a matrix, I need to add the row value to the row value
> in previous column. Is there a way to do this?
> Here is what I want the matrix results to look like:
> year1 year2 year3 year4
> Amount 1 3 4 7
> Total 1 4 8 15
> Year is the column group value and amount is the row group value.
> Total is the field I am looking for how to calculate.
> Thanks!|||Hi Chris,
Thanks for replying.
What I wrote is a little unclear but my example is correct. They want to
add the current year to the running total of the previous years. Does that
make sense?
Thanks,
Melissa
"Chris McGuigan" wrote:
> Initially I thought you were after a running total, but looking at your
> example that doesn't appear to be the case.
> You will have to do this in the underlying query by including the two
> consecutive year's values on the same row; i.e.
> Select A.Year, Sum(A.Value), (Select Sum(B.Value) From MyTable B Where
> B.Year = A.Year-1)
> From MyTable A
> Group By A.Year
> (I'm not sure this is syntactically correct, but you get my drift)
> Having said that, mathematically what you're showing would appear
> incorrect, effectively the year2 total is included in the year4 total
> twice. What are you trying to show?
> Chris
>
> Me wrote:
> > Hi,
> >
> > For columns in a matrix, I need to add the row value to the row value
> > in previous column. Is there a way to do this?
> >
> > Here is what I want the matrix results to look like:
> >
> > year1 year2 year3 year4
> > Amount 1 3 4 7
> > Total 1 4 8 15
> >
> > Year is the column group value and amount is the row group value.
> > Total is the field I am looking for how to calculate.
> >
> > Thanks!
>|||Melissa, Yes that makes sense (logically not statistically), but I
think you may have mis-interpreted it.
In the example you have given, year 3 is neither a running total nor a
grand total. It is year1 + year2 + year1 + year3, that's the effect of
what you are doing.
So they want the current year added to each past year? I still can't
see a reason for that, but hey! the customers always right!
You are better off doing this in the query. Matrix controls like fairly
simply structured data, so hide the complexity in the query, on the
lines of my original post.
The more I look at this, the more I think you probably just need a
running total. If that is the case use =RunningValue( ... ) in the cell.
Chris
Me wrote:
> Hi Chris,
> Thanks for replying.
> What I wrote is a little unclear but my example is correct. They
> want to add the current year to the running total of the previous
> years. Does that make sense?
> Thanks,
> Melissa
>
> "Chris McGuigan" wrote:
> > Initially I thought you were after a running total, but looking at
> > your example that doesn't appear to be the case.
> >
> > You will have to do this in the underlying query by including the
> > two consecutive year's values on the same row; i.e.
> > Select A.Year, Sum(A.Value), (Select Sum(B.Value) From MyTable B
> > Where B.Year = A.Year-1)
> > From MyTable A
> > Group By A.Year
> > (I'm not sure this is syntactically correct, but you get my drift)
> >
> > Having said that, mathematically what you're showing would appear
> > incorrect, effectively the year2 total is included in the year4
> > total twice. What are you trying to show?
> >
> > Chris
> >
> >
> >
> > Me wrote:
> >
> > > Hi,
> > >
> > > For columns in a matrix, I need to add the row value to the row
> > > value in previous column. Is there a way to do this?
> > >
> > > Here is what I want the matrix results to look like:
> > >
> > > year1 year2 year3 year4
> > > Amount 1 3 4 7
> > > Total 1 4 8 15
> > >
> > > Year is the column group value and amount is the row group value.
> > > Total is the field I am looking for how to calculate.
> > >
> > > Thanks!
> >
> >
