3 Replies Latest reply: Aug 31, 2013 10:32 PM by Karl Pover RSS

    Using Peek to get a Cummuliative Number

    Steve Zagzebski

      I have the following script

       

      CummulativeCommission:

      Load
      [Temp Dept],
      [Temp Cust],
      [Temp Commission DeptCust],
      [Temp Commission Dept],
      if(previous([Temp Dept])=[Temp Dept],rangesum([Temp Commission DeptCust],peek('[Temp CumCommission]')),[Temp Commission DeptCust]) as [Temp CumCommission],
      if(previous([Temp Dept])=[Temp Dept],rangesum([Temp Commission DeptCust],peek('[Temp CumCommission]')),[Temp Commission DeptCust])/[Temp Commission Dept] as [Commission Dept CummPerc]
      Resident ProductionByDeptByCust
      Order by [Temp Dept] asc, [Temp Commission DeptCust] desc;

       

      I want the field "[Commission Dept CummPerc]" to return a cumulative percent by grabbing the previous record (when "Temp Dept" = the previous "Temp Dept" field.

      Unfortunately it is not working properly and the "Temp CumCommission" field is not bringing in not cummulating the commssion in the "Temp CumCommission" field it is only bringing in the current records Commission. Botto line for some reason it is not performing the rangesum as part of the if statement but I can't figure out why.