topic Chart (Pivot or Straight table) Calculations using prior row in QlikView

Chart (Pivot or Straight table) Calculations using prior row

ysiroong — Thu, 20 May 2010 16:03:59 GMT

<body>I need to create a straight table with 1 dimension and 2 expressions (a and b), 1 of which relies on the results of the previous row. The second expression would be previous(b) + a. Is there a way to do this? Example below <col span="3" width="64"></col> <tr height="17"> <td width="64" height="17"> <table width="192" cellpadding="0" cellspacing="0" border="0"> <tbody> <tr> <td valign="bottom" width="64"> dimen </td> <td valign="bottom" width="64"> a </td> <td valign="bottom" width="64"> b </td> </tr> <tr> <td valign="bottom" width="64"> 1 </td> <td valign="bottom" width="64"> 5 </td> <td valign="bottom" width="64"> 20 </td> </tr> <tr> <td valign="bottom" width="64"> 2 </td> <td valign="bottom" width="64"> 2 </td> <td x:fmla="=C2+B3" valign="bottom" width="64"> 22 </td> </tr> <tr> <td valign="bottom" width="64"> 3 </td> <td valign="bottom" width="64"> 12 </td> <td x:fmla="=C3+B4" valign="bottom" width="64"> 34 </td> </tr> <tr> <td valign="bottom" width="64"> 4 </td> <td valign="bottom" width="64"> 6 </td> <td x:fmla="=C4+B5" valign="bottom" width="64"> 40 </td> </tr> <tr> <td valign="bottom" width="64"> 5 </td> <td valign="bottom" width="64"> 4 </td> <td x:fmla="=C5+B6" valign="bottom" width="64"> 44 </td> </tr> <tr> <td valign="bottom" width="64"> 6 </td> <td valign="bottom" width="64"> 5 </td> <td x:fmla="=C6+B7" valign="bottom" width="64"> 49 </td> </tr> <tr> <td valign="bottom" width="64"> 7 </td> <td valign="bottom" width="64"> 5 </td> <td x:fmla="=C7+B8" valign="bottom" width="64"> 54 </td> </tr> <tr> <td valign="bottom" width="64"> 8 </td> <td valign="bottom" width="64"> 9 </td> <td x:fmla="=C8+B9" valign="bottom" width="64"> 63 </td> </tr> <tr> <td valign="bottom" width="64"> 9 </td> <td valign="bottom" width="64"> 7 </td> <td x:fmla="=C9+B10" valign="bottom" width="64"> 70 </td> </tr> <tr> <td valign="bottom" width="64"> 10 </td> <td valign="bottom" width="64"> 7 </td> <td x:fmla="=C10+B11" valign="bottom" width="64"> 77 </td> </tr> </tbody> </table> </td> <td width="64"> </td> <td width="64"> </td> </tr> </body>

Chart (Pivot or Straight table) Calculations using prior row

Thu, 20 May 2010 16:12:43 GMT

Try this:

=Above(B) + A

Note that the above function will return a NULL value on the first row, so you might want to incorporate protection against that:

=IF(RowNo()=1 , A , Above(B)+A )

I haven't tried this, but it should work I think,

Chart (Pivot or Straight table) Calculations using prior row

ysiroong — Thu, 20 May 2010 16:29:00 GMT

Doesn't seem to be working. I used this formula:

Sum(If(dimension= 1, x, Above(b))) + a

and get null through out. when I remove the above(b) section from the formula the first row gets calculated while the rest of the rows = a.

dimen	a	b
1	5	20
2	2	2
3	12	12
4	6	6
5	4	4
6	5	5
7	5	5
8	9	9
9	7	7
10	7	7

Chart (Pivot or Straight table) Calculations using prior row

Thu, 20 May 2010 16:39:16 GMT

Not sure why you used Dimension=1 rather than RowNo()=1 (if you change the sort order of the table this won't work) and also, your dimension is called dimen not dimension.

See attached, pretty sure this is doing what you want.

Chart (Pivot or Straight table) Calculations using prior row

ysiroong — Thu, 20 May 2010 16:48:00 GMT

The dimension/expression names are irrelevant to the formula. They're just place holders. I used RowNo() = 1 at first and still ended up with the same result.

I chose to use dimension = 1 because there are more than 1 dimension in my actual formula so it actually looks more like

dim1 dim2

1 1

1 2

1 3

1 4

2 1

2 2

2 3

2 4

And my query would be if (dim2 = 1, x, above())

Chart (Pivot or Straight table) Calculations using prior row

Thu, 20 May 2010 16:53:09 GMT

OK, try the attached, the table at the bottom in the middle should be what your looking for.

Chart (Pivot or Straight table) Calculations using prior row

ysiroong — Thu, 20 May 2010 16:54:05 GMT

<body>I just looked at the qvw you sent and the results aren't what I need. If I start with x = 15, the very first row would be B = x + A = 20, then starting at the second row I need the prior B + A so the results would be 20 + 2, then the third row would be 22 + 12, etc. <col width="98"></col> <col width="98"></col> <col width="98"></col> <tr> <td height="17" class="xl22" width="98"> <table width="296" cellpadding="0" cellspacing="0" border="0"> <tbody> <tr> <td valign="bottom" width="99"> Dimen </td> <td valign="bottom" width="99"> sum(a) </td> <td valign="bottom" width="99"> Results </td> </tr> <tr> <td valign="bottom" width="99"> 1 </td> <td valign="bottom" width="99"> 5 </td> <td valign="bottom" width="99"> 20 </td> </tr> <tr> <td valign="bottom" width="99"> 2 </td> <td valign="bottom" width="99"> 2 </td> <td valign="bottom" width="99"> 22 </td> </tr> <tr> <td valign="bottom" width="99"> 3 </td> <td valign="bottom" width="99"> 12 </td> <td valign="bottom" width="99"> 34 </td> </tr> <tr> <td valign="bottom" width="99"> 4 </td> <td valign="bottom" width="99"> 6 </td> <td valign="bottom" width="99"> 40 </td> </tr> <tr> <td valign="bottom" width="99"> 5 </td> <td valign="bottom" width="99"> 4 </td> <td valign="bottom" width="99"> 44 </td> </tr> <tr> <td valign="bottom" width="99"> 6 </td> <td valign="bottom" width="99"> 5 </td> <td valign="bottom" width="99"> 49 </td> </tr> <tr> <td valign="bottom" width="99"> 7 </td> <td valign="bottom" width="99"> 5 </td> <td valign="bottom" width="99"> 54 </td> </tr> <tr> <td valign="bottom" width="99"> 8 </td> <td valign="bottom" width="99"> 9 </td> <td valign="bottom" width="99"> 63 </td> </tr> <tr> <td valign="bottom" width="99"> 9 </td> <td valign="bottom" width="99"> 7 </td> <td valign="bottom" width="99"> 70 </td> </tr> <tr> <td valign="bottom" width="99"> 10 </td> <td valign="bottom" width="99"> 7 </td> <td valign="bottom" width="99"> 77 </td> </tr> </tbody> </table> </td> <td class="xl22" width="98"> </td> <td class="xl22" width="98"> </td> </tr> </body>

Chart (Pivot or Straight table) Calculations using prior row

ysiroong — Thu, 20 May 2010 17:12:27 GMT

Actually, I just played around with the formula a bit and found what I did wrong. It should have been If(dim2 = 1, Sum(X), Above()) + Sum(A), whereas I put the Sum before the If.

Thanks for the help!