topic Re: Calculation on run time in QlikView

Calculation on run time

Wed, 04 Mar 2015 10:54:25 GMT

disb	wth	balance
3000	0	3000
0	3000	0
3000	0	3000
3000	0	6000
0	3000	3000

Dear All

I have a problem how i do this, calculate balance column on run time like balance=disb-wth for first row

and for the remaining rows balance=balance+(disb-wth)

how i reference the first row balance.

Prompt response will be highly appreciated.

Thanks in advance

Re: Calculation on run time

Wed, 04 Mar 2015 19:08:35 GMT

Create row number while loading the data and use that row num as part of your expression.

Thanks

Re: Calculation on run time

maxgro — Wed, 04 Mar 2015 19:16:17 GMT

load *, rowno() as id inline [

disb, wth, balance

3000, 0, 3000

0, 3000, 0

3000, 0, 3000

3000, 0, 6000

0, 3000, 3000

];

NoConcatenate load

disb - wth + alt(peek(newbalance),0) as newbalance

resident a

order by id;

drop Table a;

Re: Calculation on run time

Thu, 05 Mar 2015 06:38:36 GMT

Thanks for the reply

i dont want to add an line table i do this in pivot table.I have one dimenshion and

the disb and wth are my expressions and i have to calculate the balance on run time like

credit-debit=balance

Re: Calculation on run time

jagan — Thu, 05 Mar 2015 08:46:05 GMT

Hi,

You can do this by using Rangesum() and Above() in expression. If you attach the sample file it would be easier to give solution.

Regards,

Jagan.

Re: Calculation on run time

mukesh24 — Thu, 05 Mar 2015 09:02:32 GMT

Hi Asif,

These can be done at backend level & frontend also -->

In backend reading record level value

In frontend through ABove(),Rangesum()

Regards,

Mukesh Chaudhari

Re: Calculation on run time

Thu, 05 Mar 2015 09:46:39 GMT

Date	disb amt	wth amt	Balance
10-07-2012	3000	3000	0
14-02-2013	3000	0	3000
18-02-2013	0	3000	-3000
27-06-2013	3000	0	3000
16-07-2013	0	3000	-3000
26-07-2013	3000	0	3000
06-08-2013	0	3000	-3000
28-12-2013	3500	0	3500
07-01-2014	0	3000	-3000
15-03-2014	3700	0	3700
21-03-2014	0	4000	-4000
27-03-2014	3600	0	3600
28-03-2014	0	3500	-3500
05-06-2014	3600	0	3600
12-06-2014	0	3500	-3500
04-09-2014	4500	0	4500
08-09-2014	0	4500	-4500
18-12-2014	4500	0	4500
25-12-2014	0	4500	-4500
	35400	35000	400

Thanks for reply

My scenario is like this

disb amt-wth amt = balance

on line three it shows -3000 but according to calculation it shows 0 because on date 14-02-2013, 3000 is disb and on date 18-02-2013 3000 is wth so balance is 0.

Re: Calculation on run time

jagan — Thu, 05 Mar 2015 10:05:19 GMT

Hi,

Try like this

Chart - Straight Table

Dimension : Date

Expressions :

Deposit =Sum([disb amt])

Withdrawl =Sum([wth amt])

Balance = Alt(Above([Balance]), 0) + [Deposit] - [Withdrawl]

Please find attached file for reference

Regards,

Jagan.

Re: Calculation on run time

Thu, 05 Mar 2015 10:46:32 GMT

Bundle of thanks it works

Re: Calculation on run time

jagan — Thu, 05 Mar 2015 10:51:47 GMT

Hi,

Please close this thread by Clicking Correct and Useful Answer buttons to the useful posts.

REgards,

Jagan.