RangeSum

[Anonymous]

Hello!

I have a little problem with the Rangesum(above...)

The First value of the A is calulated as Count({<Year={´<2016´}>}ID)+B-C, so 50+10+20.

          A      B      C

          40    10     20

          ?      20     40

          ?      30     50

          ?      20     10

But the folowing values of A should be acumulative, starting with the value od the first row of A, so I should have this results:

          A      B      C

          40    10     20

          20     20     40  A2= A1+B2-C2

          0      30     50   A3= A2+B3-C3

          10    20     10    A4= A3+B4-C4

I´ve tried with

IF(Rowno()=1

,Count({<Year={´<2016´}>}ID)+B-C

,Rangesum(Above(Count({<Year={´<2016´}>}ID)+B-C))+B-C)

It works for the second row but in the other ones it doesn´t work.

Thank you!

[sunny_talwar]

Would you be able to share a sample where you might have been trying this out, your input and output are little confusing. What is ID here? May be a sample will clarify all the doubts that we have.

[Anonymous]

Hello Sunny T,

Ok, I´m going to try to explain it a little bit more:

Month       Year               #Heads                In         Out

1              2016                100                    50          20

2              2016                200                    200        100

3              2016                150                    50          100

4              2016                250                    200        100

The first row of Heads is the total of Id´s till 2016, so Count({Year={´<2016´}>}ID) + In - Out.

The problem starts with the following rows.

The heads from February till December depends on the above value of Heads, so

Heads of February: (Heads of January)+ In of February - Out of February.

I tried with

IF(Rowno()=1

,Count({Year={´<2016´}>}ID) + In - Out.   //The amount of Id´s til 2016+In January-Out january

,Rangesum

     (Above

          (Count({<Year={´<2016´}>}ID)+In+Out) // The calcul of the first month

                    +In - Out.) //+the In and Out of the foloowing month, so February

Thanks.

[sunny_talwar]

Again I don't understand what is ID here? May be if you can point out the output, we may be able to better understand your requirement. Is this what you want?

Month       Year               #Heads                In         Out               Output

1              2016                100                    50          20                  130

2              2016                200                    200        100                 230

3              2016                150                    50          100                 180

4              2016                250                    200        100                  280

[swuehl · Accepted solution]

Maybe something like

=Count(TOTAL {<Year={´<2016´}>}ID) + Rangesum(Above(TOTAL  (In - Out) , 0, Rowno(TOTAL) ))

Context is important as always, so the correct solution will depend where you are using the expression (in the context of what dimensions etc.).

[Anonymous]

Hello Stefan!!

Thank you very much!!

It worked very well, I really apreciate, I have been stopped with this problem 2 days !!