Calculate difference between two rows

Hi,

i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.

Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why?!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*

Thanks,

sluggy

*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.

Hi sluggy,

I Have the same problem now.

Have you found a solution?

Thanks

|||

I sure did. I used some custom code, on each row i called the function with the value from that row, and the function kept track of the previous values in an array.

If you can wait for about 24 hours from the time of this post, i will be able to post the function code for you.

|||Yes please send me the code.|||

Here you go....

think of this as measuring the change in booked theatre seats - i have the amount booked, and the capacity of the theatre. In the matrix field that contains the calculation, i have this as an expression:

=FormatPercent(

Code.CalcChangeInFill( Sum(Fields!Booked.Value), Sum(Fields!Capacity.Value), RowNumber("MyMatrix") ),

2,

true,

false,

true

)

and then in the code i have this:

Dim bookedVals() As Decimal

Dim capacityVals() As Decimal

Function CalcChangeInFill(ByVal bookedAmt As Decimal, ByVal capacityAmt As Decimal, ByVal rowNum as Integer) As Decimal

Dim upper As Integer

upper = 0

On Error Resume Next

If rowNum > 1 Then

upper = UBound(bookedVals) + 1

End If

ReDim Preserve bookedVals(upper)

bookedVals(upper) = bookedAmt

ReDim Preserve capacityVals(upper)

capacityVals(upper) = capacityAmt

If upper > 0 Then

If capacityVals(upper) = capacityVals(upper - 1) Then

If capacityVals(upper) = 0 Then

CalcChangeInFill = 0

Else

CalcChangeInFill = (bookedVals(upper) - bookedVals(upper - 1)) / capacityVals(upper)

End If

Else

If capacityVals(upper) = 0 Then

CalcChangeInFill = -100

ElseIf capacityVals(upper - 1) = 0 Then

CalcChangeInFill = 100

Else

CalcChangeInFill = (bookedVals(upper) / capacityVals(upper)) - (bookedVals(upper - 1) / capacityVals(upper - 1))

End If

End If

Else

CalcChangeInFill = 0

End If

End Function

This calculation got a little complicated because the capacity of the theatre could change (i.e. extra seats were added). I tracked the rowNum because this would indicate the start of a matrix when i had multiple matrices (i.e. i had a list control grouping by Week, so each matrix would have a week's worth of data in it, each row would represent one day of bookings) - at the start of each matrix the row number is 1, so i would know to "restart" the arrays and the calculation.

I hope this helps :)

|||any ideas on getting this to work on columns?

the columns in my matrix are calendar year with a sub-group that sums a couple of values. I need the percentage increase/decrease in these sums per year.

thnx in advance|||

Hi

i've just done this by creating a function that takes the current value in
the column and puts it into an array. The columns populate from left to right and
top to bottom.

So you simply need to put put in a check depending on the number of columns that
are there. I know its dynamic but I mean IE:

GROUP YEAR

InvoiceQty SupplyQty

xxxxx xxxxxxxx

When doing the calculation put a table next to matrix with the same groupings
and call a function to get the values from the array and do a calculation on them.

If i'm unclear just tell me what u need..

Gerhard Davids

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"