topic Re: Sum a value influenced by the same column values in QlikView

Sum a value influenced by the same column values

adamjiawen — Wed, 02 Apr 2014 08:58:15 GMT

Hello Everyone, During Learning to use Qlikview ,I've got a problem ,Hope for everyone's Help Thanks

First,I've a table like this without column D
----------------------------------------------

A B C

1 20 10

1 30 20

1 40 30

2 10 2

2 15 4

2 20 6

3 10 3

3 20 6

3 25 9

-----------------------------------------------

Then I want to handle the table into the following table,and there's rule in handling the table that is

-----------------------------------------------

A B C D

1 20 10 (B-C-D) 10 A new start

1 30 20 (B-C-D) 30-20-10=0 the D in this row is all the D above this row

1 39 30 (B-C-D) 39-30-10-0= -1 the D in this row is all the D above this row

2 10 2 8

2 15 4 3

2 20 6 3

3 10 3 7

3 20 6 7

3 25 9 2

---------------------------------------------------

I handle the table in scripts ,So Hope for scripts in details to achieve this function ,Thank you very much for your help!!!

Re: Sum a value influenced by the same column values

Wed, 02 Apr 2014 09:08:18 GMT

PFA

Re: Sum a value influenced by the same column values

adamjiawen — Wed, 02 Apr 2014 09:24:29 GMT

Great,but I've made a mistake in the first post,What I really mean is to get total of the Ds of the same A above the current row reduced.

You have used the rowno which means it only works in the first line ,for the other same A like 2、3 it doesn't work.

Re: Sum a value influenced by the same column values

Wed, 02 Apr 2014 09:27:44 GMT

can you mark this thread as Answered?

Re: Sum a value influenced by the same column values

MK_QSL — Wed, 02 Apr 2014 09:34:22 GMT

TEMP:

Load * Inline

[

A, B, C

1 ,20, 10

1 ,30, 20

1 ,39, 30

2 ,10, 2

2 ,15 , 4

2 ,20, 6

3 ,10, 3

3 ,20, 6

3 ,25, 9

];

NoConcatenate

FINAL:

Load

B-C as D1,

IF(A<>Previous(A), B-C, B-C-Peek(D1)) as D

Resident TEMP Order By A,B,C;

Drop Field D1;

Drop table TEMP;

Re: Sum a value influenced by the same column values

adamjiawen — Wed, 02 Apr 2014 09:43:45 GMT

Thank you guys ,Ramkumar,Manish , thank you very much for your help.