Get Previous Value for details section only.
Hello, I'm trying to get the current value of a detailed row and / it by the previous value. It seems to work BUT i want to reset the calculation with each change in a group value. Here is an example: It works for the First Group Value of Department: DEPT_0018 (it should always be "nothing" for the First week as there is not previous week. But what happens in the second value of the Group (DEPT_0019) it get the last week (52) from the previous grouping and / by the current week in the next grouping(1). I want it to only to the / within scope of a group. Let me know if you have any questions.
October 26th, 2012 3:44pm

Just modify the formula: =IIf(Previous(Fields!GroupField.Value)=Fields!GroupField.Value,Fields!Detail.Value/Previous(Fields!Detail.Value),"No Previous Week") This checks to see if you are in the same group as the previous row and only calculates the ratio if you are. Otherwise it will display a message (of your choice). It will take care of the #error as well. You may also want to check for divide by zero errors: =IIf(Previous(Fields!GroupField.Value)=Fields!GroupField.Value,IIf(Previous(Fields!Detail.Value)<>0,Fields!Detail.Value/Previous(Fields!Detail.Value),"Can't divide by zero"),"No Previous Week")Please Mark posts as answers or helpful so that others can more easily find the answers they seek.
Free Windows Admin Tool Kit Click here and download it now
October 26th, 2012 4:17pm

Hello Tim, This works now...Kinda...one more issue to cover. In the first grouping the first values show #Error as there is no previous week or previous group. How do I handle that?
October 26th, 2012 4:26pm

Try: =IIf(Not(IsNothing(Previous(Fields!GroupField.Value))),IIf(Previous(Fields!GroupField.Value)=Fields!GroupField.Value,IIf(Previous(Fields!Detail.Value)<>0,Fields!Detail.Value/Previous(Fields!Detail.Value),"Can't divide by zero"),"No Previous Week"),"No Previous Week") Or: =IIf(Not(IsNothing(Previous(Fields!GroupField.Value))) And Previous(Fields!GroupField.Value)=Fields!GroupField.Value,IIf(Previous(Fields!Detail.Value)<>0,Fields!Detail.Value/Previous(Fields!Detail.Value),"Can't divide by zero"),"No Previous Week")Please Mark posts as answers or helpful so that others can more easily find the answers they seek.
Free Windows Admin Tool Kit Click here and download it now
October 26th, 2012 5:36pm

Try: =IIf(Not(IsNothing(Previous(Fields!GroupField.Value))),IIf(Previous(Fields!GroupField.Value)=Fields!GroupField.Value,IIf(Previous(Fields!Detail.Value)<>0,Fields!Detail.Value/Previous(Fields!Detail.Value),"Can't divide by zero"),"No Previous Week"),"No Previous Week") Or: =IIf(Not(IsNothing(Previous(Fields!GroupField.Value))) And Previous(Fields!GroupField.Value)=Fields!GroupField.Value,IIf(Previous(Fields!Detail.Value)<>0,Fields!Detail.Value/Previous(Fields!Detail.Value),"Can't divide by zero"),"No Previous Week")Please Mark posts as answers or helpful so that others can more easily find the answers they seek.
October 26th, 2012 5:36pm

Try: =IIf(Not(IsNothing(Previous(Fields!GroupField.Value))),IIf(Previous(Fields!GroupField.Value)=Fields!GroupField.Value,IIf(Previous(Fields!Detail.Value)<>0,Fields!Detail.Value/Previous(Fields!Detail.Value),"Can't divide by zero"),"No Previous Week"),"No Previous Week") Or: =IIf(Not(IsNothing(Previous(Fields!GroupField.Value))) And Previous(Fields!GroupField.Value)=Fields!GroupField.Value,IIf(Previous(Fields!Detail.Value)<>0,Fields!Detail.Value/Previous(Fields!Detail.Value),"Can't divide by zero"),"No Previous Week")Please Mark posts as answers or helpful so that others can more easily find the answers they seek.
Free Windows Admin Tool Kit Click here and download it now
October 26th, 2012 5:42pm

Hello Tim, Sorry for the late reply. Both did not work. Would it not be easier to say something like: If row number = 1 then nothing else do the calculation?
October 29th, 2012 8:46am

Hello Tim, This seemed to work for me: =IIF(Previous(Fields!AP_Avg_Str_Reg_Sales_Units.Value) = 0 OR IsNothing(Previous(Fields!AP_Avg_Str_Reg_Sales_Units.Value)) or Previous(Fields!Class_Triple_Nested_Hierarchy_Id.Value)<>Fields!Class_Triple_Nested_Hierarchy_Id.Value , "" ,Fields!AP_Avg_Str_Reg_Sales_Units.Value / (IIF(Previous(Fields!AP_Avg_Str_Reg_Sales_Units.Value) = 0,1,Previous(Fields!AP_Avg_Str_Reg_Sales_Units.Value))) ) AS a backup I also created a function: Public Shared Function WeeklyBuild(ByVal CurrentSales As Decimal, ByVal PreviousSales As Decimal, ByVal RowNum As integer) As String If RowNum = 1 or PreviousSales = 0 or isnothing(PreviousSales ) Then Return nothing End If Return ( CurrentSales / PreviousSales) End Function
Free Windows Admin Tool Kit Click here and download it now
October 29th, 2012 10:45am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics