Please Help, continous rangesum 2 table

[#1]

Hello Everyone,

Can anyone help me in writing the 'Expression'  Script ?

Please see my table (database using SQL) :

the first row in credit (yellow highlight),

it came from  first credit = -5.325.019.926(1st credit/LSALDO) - 219.242.320(1st Income) = -5.105.777.606.

this is my script on CREDIT expression :

rangegesum(above(sum(If(INCOME<1,(LSALDO-OUTCOME),(LSALDO+INCOME))),0,rowno()))  but it came wrong from my expectation.. 

LSALDO = -5.325.019.926

1. I want the value 2nd row in the CREDIT = -5.105.777.606(from the first row in 'CREDIT') - 31.822.991(from the 2nd row in 'INCOME') =  5.073.954.615 <- iwant the value like this. like continuous substraction.

     then the third row value is =

-5.073.954.615(from the 2nd row in 'CREDIT' - 31.822.991(from the 3rd row in 'INCOME') = -5073954615

2. and if the income <1 or null the CREDIT is - OUTCOME

I hope everybody understand what I mean and sory for my bad english

Thanks & Best Regards

[sunny_talwar]

Would you be able to provide the above data in a Excel file so that we can test load the data and check what expression will work the best.

Best,

Sunny

[#3]

Edited. I inser attachment(sample file.xls) for sample.

thanks for advice Mr. Sunny T

[settu_periasamy]

Hi,

May be try this...

if(RowNo()=1,

  if(INCOME<1,

  (LSALDO-OUTCOME),

  (LSALDO+INCOME)),

  if(INCOME<1,Rangesum(Above(Column(4)),-Sum(OUTCOME)),

  Rangesum(Above(Column(4)),Sum(INCOME))))

Here, i created the variable LSALDO  value as -5325019926

[qlikviewwizard]

Hi Nice Guy, Did you resolve the issue?

[#6]

sorry for late reply(coz I have another assignment).

not yet, the value in my qlikview is different. but I using script from mr/mrs Settu Perisiamy. the value is right (in table from Settu Perisiamy), but I'm still searching why value in my qlikview is different... 

[#7]

in your script :

if(RowNo()=1,

  if(INCOME<1,

  (LSALDO-OUTCOME),

  (LSALDO+INCOME)),

  if(INCOME<1,Rangesum(Above(Column(4)),-Sum(OUTCOME)),

  Rangesum(Above(Column(4)),Sum(INCOME))))

at column(4), what '4' is ? sum(CREDIT) or if(RowNo()=1, ? are column Row Count? 

Thanks before..

[settu_periasamy]

Hi,

for the first row, you need to calculate based on your LSALDO value .

Column(4) is your current expression Column Number.

[#9]

Hi Mr/Mrs Settu periasamy,

I'm using ur script, and matching with my table. but the value(answer) still with the script I make before. I think

script "above" doesn't work in the table,coz the first row value is right,then, second row the value not using value in the first row (still using first value of LSALDO = -5.325.019.926)

if(RowNo()=1,

  if(INCOME<1,

  (LSALDO-OUTCOME),

  (LSALDO+INCOME)),

  if(INCOME<1,Rangesum(Above(Column(4)),-Sum(OUTCOME)),

  Rangesum(Above(Column(4)),Sum(INCOME))))

row,income,outcome,rangesum is 'Expression'

I'm still confuse, I use your script, but the value doesn't same : (

is SQL database take effect with this script? because I'm copy the sql database to Excel, then I create same table in qlikview ,same script, and voila the answer is right.. but I don't want to use Excel, I still want to use SQL database..

thx before

[#10]

Hi mr/mrs Settu Periasamy,

i think i found the error problem. it becouse i dont have RowNo() in my qlikview. why 'rowno()' , becouse i try reload data in excel, with the same value, script expression from u, and also 'LOAD RowNo() in main script. then the answer is right.

is there any script for SQL database to create rowno() like this?

LOAD  RowNo(),​

     INCOME,

     OUTCOME,

FROM

D:\asdf.xls

(biff, embedded labels, table is Sheet1$);

Thanks