Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
guiecokey22
New Contributor III

RangeSum

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!

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: RangeSum

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.).

5 Replies

Re: RangeSum

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.

guiecokey22
New Contributor III

Re: RangeSum

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.

Re: RangeSum

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

MVP
MVP

Re: RangeSum

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.).

guiecokey22
New Contributor III

Re: RangeSum

Hello Stefan!!

Thank you very much!!

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