16 Replies Latest reply: Sep 13, 2011 11:34 AM by Marc Livingston RSS

    Need to use accumulation of another expression in expression.

      I have an issue. I was able to get the results I want on crystal using a running total. I am now trying to recreate this is Qlikview, but I am running into issues.

      I am not sure how to use running totals in qlikview, so currently the following formula is returning 0 since I do not know how to tell it that I need the running total amount.

       

      Should be the accumulated total of the balance minus the current fiscal periods balance.

       

      Qlikview formula for Opening balance (Should be previous periods closing balance):

      (if (sum(balanceamt-openbalance)=0 ,  sum(balanceamt) , sum((balanceamt-openbalance)- (balanceamt))))

       

       

      Crystal Formula:

       

      if {#RTotal1}=0 then

      Sum ({BAQReportResult.GLPeriodBal.BalanceAmt}, {BAQReportResult.GLPeriodBal.FiscalPeriod})

      else {#RTotal1}- Sum ({BAQReportResult.GLPeriodBal.BalanceAmt}, {BAQReportResult.GLPeriodBal.FiscalPeriod})

       

      {#RTotal1}:

      {BAQReportResult.GLPeriodBal.BalanceAmt}-{BAQReportResult.GLPeriodBal.OpenBalance}

       

       

      If this is not clear, I have a straight table with dimensions Fiscal Year and Fiscal Period.

      Opening balance is defined as the accumulation total of the balance at that point minus the current periods balance

      Closing Balance is the accumulated total of the balance at that point

      Current balance is the sum of the balance for only that period

      Ex:

      Year  Period  open balance  current balance  Closing Balance

      2010 1          0                      250                      250

      2010 2          250                  774,500               774,750    

      2010 3          774,750            937,774              162,994               

       

       

       

       

      Edit:

       

      Is there a way to use set analysis to take the sum of all values less than the current dimension value of the table?