1 Reply Latest reply: Nov 19, 2010 6:25 PM by John Witherspoon RSS

    Dividing accumulating expressions

      Hi, I have the same problem

      I have an expression as follows: its name is "CASH FLOW"

       

      sum ( {<class = {a}>} [Balance] / sum ( {<{class = {b}>} [Balance])

      when I accumulate = it accumulates the ratios - which is not what I want

       

      I want - "cumulative" sum ( {<class = {a}>} [Balance] / "cumulative" sum ( {<{class = {b}>} [Balance])

      The dimension is period i.e. Year 1, 2, 3 etc

        • Dividing accumulating expressions
          John Witherspoon

          From the other thread:

           


          John Witherspoon wrote: Rangesum(above()) should still work. Create separate hidden expressions for the class a and class b balances, then rangesum each over the current row and all above rows. Probably something like this:
          Class A Balance = sum({<class={'a'}>} Balance) // hidden expression
          Class B Balance = sum({<class={'b'}>} Balance) // hidden expression
          Cash Flow = rangesum(above("Class A Balance"),0,rowno())
          /rangesum(above("Class B Balance"),0,rowno())
          A better approach might be to handle it with data, though. Let's say your first year was 2001:
          AsOfYear, Year
          2001, 2001
          2002, 2001
          2002, 2002
          2003, 2001
          2003, 2002
          2003, 2003
          etc.
          Use AsOfYear in the chart, and the sums will be a full accumulation (don't set the full accumulation option), and will work when dividing the sums.