Friday, March 9, 2012

Matrix calculation using more than one column

I have a matrix of data for a period of months. What I am trying to do is to
add a column at the end which will be YOY change. So how in a matrix do I
reference the value in column 4 (for example) and the last column. Also how
do I add this calculated column to the end of the matrix.For the column at the end, right click your column group and click Subtotal.
If your months show only one field, make sure the expression for this field
is =sum(Fields!MyField.value), and the total column will sum all the months
in the last column.
I'm not sure what you mean by YOY change. If this is something else than
summing the months, you need to use the InScope function to tell the matrix
what to display in which type column, in the details cell.
If your column group is called matrix_Months, you could try
=IIF(InScope("matrix_Months"), Fields!MyField.Value, (your YOY change
calculation))
Not quite sure on how to reference specific columns. What do you need this
for?
Kaisa M. Lindahl Lervik
"stu..." <stu@.discussions.microsoft.com> wrote in message
news:F82841D5-2FA1-4E78-8D1C-31FBEBB5E397@.microsoft.com...
>I have a matrix of data for a period of months. What I am trying to do is
>to
> add a column at the end which will be YOY change. So how in a matrix do I
> reference the value in column 4 (for example) and the last column. Also
> how
> do I add this calculated column to the end of the matrix.
>|||Kaisa,
What I have is
Col1 Col2 Col3 Col4 ..... Col12 YOY
1 2 6 9 16 5
The last column YOY is year on year change so this will be value in col12
minus the value in col4 - for example. So how in a matrix do you get it to
look at the values in two different columns.
Stu...
"Kaisa M. Lindahl Lervik" wrote:
> For the column at the end, right click your column group and click Subtotal.
> If your months show only one field, make sure the expression for this field
> is =sum(Fields!MyField.value), and the total column will sum all the months
> in the last column.
> I'm not sure what you mean by YOY change. If this is something else than
> summing the months, you need to use the InScope function to tell the matrix
> what to display in which type column, in the details cell.
> If your column group is called matrix_Months, you could try
> =IIF(InScope("matrix_Months"), Fields!MyField.Value, (your YOY change
> calculation))
> Not quite sure on how to reference specific columns. What do you need this
> for?
> Kaisa M. Lindahl Lervik
> "stu..." <stu@.discussions.microsoft.com> wrote in message
> news:F82841D5-2FA1-4E78-8D1C-31FBEBB5E397@.microsoft.com...
> >I have a matrix of data for a period of months. What I am trying to do is
> >to
> > add a column at the end which will be YOY change. So how in a matrix do I
> > reference the value in column 4 (for example) and the last column. Also
> > how
> > do I add this calculated column to the end of the matrix.
> >
> >
>
>|||I'm a bit slow today.
Is each column one month in the same year? Can you have months from more
than one year?
How do you group these?
And what does your dataset look like?
Kaisa M. Lindahl Lervik
"stu..." <stu@.discussions.microsoft.com> wrote in message
news:6ACE8DB6-C4D9-470F-9E90-46050B6E1218@.microsoft.com...
> Kaisa,
> What I have is
> Col1 Col2 Col3 Col4 ..... Col12 YOY
> 1 2 6 9 16 5
> The last column YOY is year on year change so this will be value in col12
> minus the value in col4 - for example. So how in a matrix do you get it to
> look at the values in two different columns.
> Stu...
> "Kaisa M. Lindahl Lervik" wrote:
>> For the column at the end, right click your column group and click
>> Subtotal.
>> If your months show only one field, make sure the expression for this
>> field
>> is =sum(Fields!MyField.value), and the total column will sum all the
>> months
>> in the last column.
>> I'm not sure what you mean by YOY change. If this is something else than
>> summing the months, you need to use the InScope function to tell the
>> matrix
>> what to display in which type column, in the details cell.
>> If your column group is called matrix_Months, you could try
>> =IIF(InScope("matrix_Months"), Fields!MyField.Value, (your YOY change
>> calculation))
>> Not quite sure on how to reference specific columns. What do you need
>> this
>> for?
>> Kaisa M. Lindahl Lervik
>> "stu..." <stu@.discussions.microsoft.com> wrote in message
>> news:F82841D5-2FA1-4E78-8D1C-31FBEBB5E397@.microsoft.com...
>> >I have a matrix of data for a period of months. What I am trying to do
>> >is
>> >to
>> > add a column at the end which will be YOY change. So how in a matrix do
>> > I
>> > reference the value in column 4 (for example) and the last column. Also
>> > how
>> > do I add this calculated column to the end of the matrix.
>> >
>> >
>>|||It will be a 12 month rolling window, so can have different years.
Grouping just by the month.
Stu...
"Kaisa M. Lindahl Lervik" wrote:
> I'm a bit slow today.
> Is each column one month in the same year? Can you have months from more
> than one year?
> How do you group these?
> And what does your dataset look like?
> Kaisa M. Lindahl Lervik
> "stu..." <stu@.discussions.microsoft.com> wrote in message
> news:6ACE8DB6-C4D9-470F-9E90-46050B6E1218@.microsoft.com...
> > Kaisa,
> >
> > What I have is
> >
> > Col1 Col2 Col3 Col4 ..... Col12 YOY
> > 1 2 6 9 16 5
> >
> > The last column YOY is year on year change so this will be value in col12
> > minus the value in col4 - for example. So how in a matrix do you get it to
> > look at the values in two different columns.
> >
> > Stu...
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> For the column at the end, right click your column group and click
> >> Subtotal.
> >> If your months show only one field, make sure the expression for this
> >> field
> >> is =sum(Fields!MyField.value), and the total column will sum all the
> >> months
> >> in the last column.
> >> I'm not sure what you mean by YOY change. If this is something else than
> >> summing the months, you need to use the InScope function to tell the
> >> matrix
> >> what to display in which type column, in the details cell.
> >> If your column group is called matrix_Months, you could try
> >> =IIF(InScope("matrix_Months"), Fields!MyField.Value, (your YOY change
> >> calculation))
> >>
> >> Not quite sure on how to reference specific columns. What do you need
> >> this
> >> for?
> >>
> >> Kaisa M. Lindahl Lervik
> >>
> >> "stu..." <stu@.discussions.microsoft.com> wrote in message
> >> news:F82841D5-2FA1-4E78-8D1C-31FBEBB5E397@.microsoft.com...
> >> >I have a matrix of data for a period of months. What I am trying to do
> >> >is
> >> >to
> >> > add a column at the end which will be YOY change. So how in a matrix do
> >> > I
> >> > reference the value in column 4 (for example) and the last column. Also
> >> > how
> >> > do I add this calculated column to the end of the matrix.
> >> >
> >> >
> >>
> >>
> >>
>
>

No comments:

Post a Comment