topic Re: Get the previous value from a column in QlikView

Get the previous value from a column

Anonymous — Sun, 28 Aug 2016 05:49:58 GMT

Hi All, I have been asked to deploy the following requirement:

A table containing 3 columns:

Item Quantity Amount

Item-A 20 12

Item-B 3 10

Item-C 6 5

Item-D 4 6

Item-E 5 2

. . .

The result should be a chart with the following output:

Item Quantity Accumulated Amount

Item-A 20 20

Item-B 3 60

Item-C 6 360

Item-D 4 1440

Item-E 5 7200

. . .

The expression of column "quantity" is : Sum(Quantity)

The expression of column "accumulated Amount" should be:

The Above value (Sum (Amount)) multiple by the current quantity.

For example, in Item-B, the above value is 20 (Sum(Amount) multiple by the current quantity: 3 and the result of the second line is 60.

The third line: above value is 60 multiply by the current quantity 6, the result is 360, etc, etc...

(btw: the value of the first line should be 2 (just the quantity) since we do not have the "above" amount)

I found difficulties to find the appropriate expression for that column in the chart. can someone give me an idea how to solve this?

many 10x.

Eli.

Re: Get the previous value from a column

Anil_Babu_Samineni — Sun, 28 Aug 2016 08:13:34 GMT

Perhaps This

Sum(Amount) * Below(TOTAL Sum(Quantity))

Re: Get the previous value from a column

Anonymous — Sun, 28 Aug 2016 08:34:07 GMT

Thanks anil, but this expression gives the following results (different from the expected result):

Item	Quantity	Accumulated Amount
Item-A	20	36
Item-B	3	60
Item-C	6	20
Item-D	4	30
Item-E	5

do you have any other direction?

thanks

Re: Get the previous value from a column

sunny_talwar — Sun, 28 Aug 2016 10:57:24 GMT

Can you explain how did you get 20 for Item-A on your list. I think you did a great job of explaining things after Item-A, but unless I get item-A right, there won't be Item-B, Item-C... etc

Re: Get the previous value from a column

Anonymous — Sun, 28 Aug 2016 11:07:21 GMT

Hi Sunny' Thank you for your response.

You are right , i did a mistake in the first line, the quantity of the first line is 20, see the correct example:

Item Quantity Accumulated Amount

Item-A 20 20

Item-B 3 60

Item-C 6 360

Item-D 4 1440

Item-E 5 7200

. . .

Now, the results should looks line this:

Item Quantity Accumulated Amount

Item-A 2 20

Item-B 3 60

Item-C 6 360

Item-D 4 1440

Item-E 5 7200

. . .

and why the results of the first line is 20 ?, because this is the first line and in this case we do not have the 'Above' so we take just the quantity.

many many thanks.

Re: Get the previous value from a column

sunny_talwar — Sun, 28 Aug 2016 15:19:44 GMT

May be this

But since I referring to my expression again, this expression is selection dependent. Check it out if this helps you achieve what you are looking to do.

Re: Get the previous value from a column

Anonymous — Sun, 28 Aug 2016 16:13:12 GMT

Yes , this is the answer. thank you

Re: Get the previous value from a column

Anil_Babu_Samineni — Sun, 28 Aug 2016 16:51:45 GMT

Sunny, What is the Expr1?

Re: Get the previous value from a column

sunny_talwar — Sun, 28 Aug 2016 17:36:31 GMT

It is the expression label. Self referencing expression where the expression is referencing itself.

Re: Get the previous value from a column

Anonymous — Wed, 12 Dec 2018 07:02:50 GMT

QlikExperts,

Want the similar solution at group level with required output as per below sample. Please assist.

ItemGroup	Item	Quantity	Output
A	Item-A	20	20
A	Item-B	3	60
A	Item-C	6	360
A	Item-D	4	1,440
A	Item-E	5	7,200
B	Item-A	12	12
B	Item-B	5	60
B	Item-C	17	1,020
B	Item-D	16	16,320
B	Item-E	8	1,30,560