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"
|||Hi,
I am also facing same problem...do u got any solution....
plz help me
sql
|||