topic Re: Please Help, continous rangesum 2 table in QlikView

Please Help, continous rangesum 2 table

Tue, 11 Aug 2015 10:27:19 GMT

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

Re: Please Help, continous rangesum 2 table

sunny_talwar — Tue, 11 Aug 2015 15:37:55 GMT

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

Re: Please Help, continous rangesum 2 table

Wed, 12 Aug 2015 01:45:41 GMT

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

thanks for advice Mr. Sunny T

Re: Please Help, continous rangesum 2 table

settu_periasamy — Wed, 12 Aug 2015 03:35:53 GMT

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

Re: Please Help, continous rangesum 2 table

qlikviewwizard — Wed, 12 Aug 2015 06:04:08 GMT

Hi Nice Guy, Did you resolve the issue?

Re: Please Help, continous rangesum 2 table

Wed, 12 Aug 2015 09:46:12 GMT

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

Re: Please Help, continous rangesum 2 table

Wed, 12 Aug 2015 09:53:02 GMT

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

Re: Please Help, continous rangesum 2 table

settu_periasamy — Wed, 12 Aug 2015 09:59:56 GMT

Hi,

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

Column(4) is your current expression Column Number.

Re: Please Help, continous rangesum 2 table

Sun, 16 Aug 2015 15:13:39 GMT

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

Re: Please Help, continous rangesum 2 table

Tue, 18 Aug 2015 05:52:38 GMT

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

Re: Please Help, continous rangesum 2 table

Tue, 18 Aug 2015 09:45:07 GMT

Hi Qlikviewwizard

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

(reload excel table)

LOAD RowNo(),

INCOME,

OUTCOME,

FROM

D:\asdf.xls

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

Thanks

Re: Please Help, continous rangesum 2 table

Wed, 26 Aug 2015 07:53:09 GMT

If you use excel table or database you can use script from mr/mrs. settu_periasamy‌

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

But, if you use SQL Database you can use my script

if(Row=1,

if(INCOME<1,

(vsaldo-outcome),

(vsaldo+income)

if(INCOME<1,

RangeSum(above(TOTAL Column(4)), - Column(3)) ,

RangeSum(above(TOTAL Column(4)), + Column(2))

)

in first line 'Row' is expression. you must create this expression for main 'IF'

the expression is 'RowNo(TOTAL)' to count your total row.

vsaldo=LSALDO

LSALDO=my 1st value of credit

thanks all for the support