June 25, 2008 at 11:46 am
I have a matrix report that shows % of mix for departments between 2 dates.
Dept Date A Date B New Colunm
A 19.30% 15.41% ?
B 11.19% 11.51% ?
Etc...
How can I create another column that shows the difference between Date A and Date B by Dept?
I know I can add a subtotal which adds the 2, but I would like the difference between the 2 columns.
seajoker
June 26, 2008 at 1:55 am
The Matrix is notorious for it's little tricks, but the way to do this is to right click on the heading of your second value column and select "Add Column". Make sure that you are selecting Add Column and not add column group which is something entirely different.
Once you have the column added, you can simply add an expression to this column that is effectively A-B to give you the difference you want.
Good luck,
Nigel West
UK
June 26, 2008 at 10:30 am
I understand your post by adding the new column, but in a matrix there is only 1 coulumn that produces the 2 values. How do I do A-B when they are the same column?
Can an expression refer to the same column but do a calculation?
Here is what my Matrix looks like
Dept % of Mix =Fields!effdate.ValueChange
=Fields!department.Value =Sum(Fields!Grandtotal.Value) ?
I want the change field to be this difference between the GrandTotal value for the effdates.
Thanks for your help.
seajoker
June 26, 2008 at 10:35 am
Can you drop a small sample of the current output you're getting, I'm not sure I fully understand what you have at the moment. I certainly don't understand how you're getting two values in a single column!!
Just type out a few lines of what your matrix looks like & I'll take a look.
Nigel West
UK
June 26, 2008 at 10:47 am
Here is what the output looks like:
Dept Date A Date B New Colunm
A 19.30% 15.41% ?
B 11.19% 11.51% ?
The % for each date is 1 column in the layout of the matrix. I posted the layout of the matrix report on the previous post.
seajoker
June 26, 2008 at 1:36 pm
AHHHHHH !
So now I understand what you mean.
A question: Are there only two dates included on any row on this report (as I'm sure you know, the concept of a matrix is that it expands to the total width of the dataset)? If the answer to this is yes then probably what you should do is to add a sub-total column and then change the expression on the sub-total column to a something like:
What I have done will allow you to place the difference value on the left side of the matrix (before the date values). I created the matrix, added the dept as a row group and the date as a column group, then added the value to the cell. This gave me the same output as you have now.
Then, I added a table in the first cell of the first row (at the moment you should see the dept field in there). This will create a three column table, and you should delete the third column. Also, to avoid any space issues, remove the table header and footer so you only have the detail lines.
Now, you should be left with one row and two columns.
Put the department field into the first column of the table.
In the second column of the table, add the following expression (or similar according to your own field names): =MAX(Fields!ValueAmount.Value) - MIN(Fields!ValueAmount.Value)
This gives you the difference immediately before the cell amounts.
You can also then create a table in the top left header area so that you can space the columns cxorrectly to put headings in for both those cells.
Let me know how you get on with that.
Nigel West
UK
June 27, 2008 at 11:10 am
Thank you for your help, I'm 90% there.
I'm having some formatting issue when I add the second table. I'm getting double departments and % different, which is 1 for each date.
Here is what it looks like:
Dept Date1 Date2
A 3.89% 19.30% 15.41%
A 3.89%
B 1.03% 21.37% 22.41%
B 1.03%
The 3.89% and 1.03% are the correct numbers, but they are appearing twice. Any advice or changes?
seajoker
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply