topic Re: How to Multiply Previous Rows in a Table in QlikView

How to Multiply Previous Rows in a Table

Tue, 09 Dec 2014 02:30:40 GMT

I have a table here:

Date Rate

2014/11/01 N1

2014/11/02 N2

2014/11/03 N3

and I wanna add a column on the right to be:

Date Rate Value

2014/11/01 N1 X1

2014/11/02 N2 X2

2014/11/03 N3 X3

which

X1 = N1,

X2 = N1 * N2,

X3 = N1 * N2 * N3,

.....

However, rangeSum doesn't help on this issue, it only solves the accumulated problem.

any one have any ideas about this?

Re: How to Multiply Previous Rows in a Table

hic — Tue, 09 Dec 2014 06:46:53 GMT

If(IsNull(Peek(Rate)), Rate, Rate*Peek(Rate)) as Value

HIC

Re: How to Multiply Previous Rows in a Table

jagan — Tue, 09 Dec 2014 07:02:58 GMT

Hi,

Try like this in script

TableName:

LOAD

If(IsNull(Peek(Value)), Rate, Rate * Peek(Value)) as Value

FROM DataSource;

Regards,

Jagan.

Re: How to Multiply Previous Rows in a Table

Tue, 09 Dec 2014 07:25:38 GMT

@HIC,@jagan mohan,

Thank you for your reply.

However, my question is how to do it in the expression,not in the script , just like we use rangeSum to solve the accumulation problems. I want the Rate and Value could re-calculater after I made any selections.

Re: How to Multiply Previous Rows in a Table

jagan — Tue, 09 Dec 2014 07:32:21 GMT

Hi,

Check this link

http://community.qlik.com/message/663555#663555

Expression Name:

If(RowNo() = 1, Sum(Rate), Rangesum(Sum(Rate) * Above([Expression Name]))

Regards,

Jagan.

Re: How to Multiply Previous Rows in a Table

MK_QSL — Tue, 09 Dec 2014 07:35:51 GMT

Test:

Load

Date(Date#(Date,'YYYY/MM/DD')) as Date,

Rate

Inline

[

Date, Rate

2014/11/01, 2

2014/11/02, 3

2014/11/03, 4

2014/11/04, 2

];

Final:

Load

Date,

Rate,

If(RowNo()=1, Rate, Peek('Value')*Rate) as Value

Resident Test

Order By Date;

Drop Table Test;

Re: How to Multiply Previous Rows in a Table

Anders_Eriksson — Tue, 09 Dec 2014 08:19:34 GMT

This as an expression in straight table gives what you want, sadly using if's but anyway;

if(RowNo() > 2, Above(Rate, 2), 1) * if(RowNo() >1, Above(Rate, 1), 1) * Rate

Re: How to Multiply Previous Rows in a Table

Tue, 09 Dec 2014 08:42:14 GMT

hi jagan

what do you mean "Above(Expression Name)"?

Re: How to Multiply Previous Rows in a Table

Tue, 09 Dec 2014 08:48:33 GMT

hi Anders

Thank you for your reply.

This expression only works when there are only 3 rows, how to do it when the table got hundreds of Rows?

原始邮件

发件人:Anders Erikssonqcwebmaster@qlikview.com

收件人:Jun Jing Susujunjing@hofan.cn

发送时间:2014年12月9日(周二) 16:20

主题:Re: - How to Multiply Previous Rows in a Table

Qlik Community

How to Multiply Previous Rows in a Table

reply from Anders Eriksson in App Development - View the full discussion

This as an expression in straight table gives what you want, sadly using if's but anyway;

if(RowNo() 2, Above(Rate, 2), 1) * if(RowNo() 1, Above(Rate, 1), 1) * Rate

Reply to this message by replying to this email, or go to the message on Qlik Community

Start a new discussion in App Development by email or at Qlik Community

Following How to Multiply Previous Rows in a Table in these streams: Inbox

Re: How to Multiply Previous Rows in a Table

jagan — Tue, 09 Dec 2014 08:58:18 GMT

Hi,

Expression Name is the title of the expression i.,e Rate

Regards,

Jagan.

Re: How to Multiply Previous Rows in a Table

Tue, 09 Dec 2014 09:08:21 GMT

it works!!! Thank you very much jagan!!!

Re: How to Multiply Previous Rows in a Table

jagan — Wed, 10 Dec 2014 03:24:23 GMT

Hi,

Please find attached file for solution in front end.

Hope this helps you.

If you got the answer please close this thread by giving Correct and Helpful answer to the useful posts.

Regards,

jagan.

Re: How to Multiply Previous Rows in a Table

smago001 — Mon, 10 Dec 2018 18:28:09 GMT

Here is my small contribution.

In my case, I could not use the SUM function, being that the content of the column already had aggregated values.

The following suggestion goes in a different column in the same table (say, a column after Value) and it would be your third column (assuming that Date is a Dimension and that Rate and Value are columns 1 and 2 from your list of expressions)

If (RowNo() = 1, Column(1), Column(1) * above(Column(3)).

I hope it helps,

Regards,

S.