Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to Multiply Previous Rows in a Table

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?

1 Solution

Accepted Solutions
jagan
Partner - Champion III
Partner - Champion III

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.

View solution in original post

12 Replies
hic
Former Employee
Former Employee

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

HIC

jagan
Partner - Champion III
Partner - Champion III

Hi,

Try like this in script

TableName:

LOAD

*,

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

FROM DataSource;

Regards,

Jagan.

Not applicable
Author

@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
Partner - Champion III
Partner - Champion III

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

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;

Anders_Eriksson
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

hi jagan

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

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

Hi,

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

Regards,

Jagan.