How to Multiply Previous Rows in a Table

[#1]

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?

[hic]

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

HIC

[jagan]

Hi,

Try like this in script

TableName:

LOAD

*,

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

FROM DataSource;

Regards,

Jagan.

[#4]

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

[jagan · Accepted solution]

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.

[MK_QSL]

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;

[anderseriksson]

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

[#8]

hi jagan

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

[#9]

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

© 1993-2014 QlikTech International AB Copyright Trademarks | Privacy | Terms of Use | Software EULA

[jagan]

Hi,

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

Regards,

Jagan.