topic Re: RangeSum in QlikView

RangeSum

Anonymous — Fri, 21 Oct 2016 12:16:19 GMT

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!

Re: RangeSum

sunny_talwar — Fri, 21 Oct 2016 15:35:32 GMT

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.

Re: RangeSum

Anonymous — Mon, 24 Oct 2016 05:38:11 GMT

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

sunny_talwar — Mon, 24 Oct 2016 19:43:52 GMT

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

Re: RangeSum

swuehl — Mon, 24 Oct 2016 20:40:44 GMT

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

Re: RangeSum

Anonymous — Tue, 25 Oct 2016 05:29:23 GMT

Hello Stefan!!

Thank you very much!!

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