Solved: Chart (Pivot or Straight table) Calculations using... - Qlik Community

ysiroong · ‎2010-05-20

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

Report Inappropriate Content · ‎2010-05-20

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,

View solution in original post

Report Inappropriate Content · ‎2010-05-20

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,

ysiroong · ‎2010-05-20

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

Report Inappropriate Content · ‎2010-05-20

Hi

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.

ysiroong · ‎2010-05-20

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())

Report Inappropriate Content · ‎2010-05-20

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

ysiroong · ‎2010-05-20

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

ysiroong · ‎2010-05-20

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!

Chart (Pivot or Straight table) Calculations using prior row