Calculated rows from the same table

[1Hunter]

Hi, 

Please find my table scenario below: 

row_no	bucket1	bucket2	obs.
row1	10	11
row2	20	21
row3	30	33	row1+row2
row4	40	41
row5	50	51
row6	90	92	row4+row5
row7	60	59	row6-row3
row8	60	119	cumulated(row7)

Please be advised that the rows(3;6;7;8) are calculated based on the other existing rows from the same table. I could easily calculate the row3 and row6 easily by creating an aggregated table in the data model which makes the sum of the specified rows. 

The question is for row7 and row8, how would you calculate them(ideally without to multiplicate the number of rows since the input table is huge~ 300.000.000 rows). 

[Aasir]

row7: sum({<row_no = {'row6', 'row3'}>} bucket1) - sum({<row_no = {'row6', 'row3'}>} bucket2)

row8: rangeavg(above(sum({<row_no = {'row7'}>} bucket1 - bucket2), 0, RowNo()))

[1Hunter]

@Aasir , thank you for your reply. How would you integrate that in the single measure formula: 

sum(measure) would return the values for row(1;2;3;4;5;6), but then how would you integrate in this formula the sum for row7? 
I tried something like this: If(row_no = 'row7', sum({<row_no={'6'}>}measure) - sum({<row_no={'3'}>}measure)), sum(measure)) but it doesn't return the values for row7.

[1Hunter · Accepted solution]

Hey, 

I think I managed to solve the issue by playing a little bit more with the expansion of the measure, what worked is something like this: 

If(row_no=7,$(=sum({<row_no=6>}measure))-$(=sum({<row_no=6>}measure))-$(=sum({<row_no=3>}measure)),sum(measure))

The issue seems to be that Qliksense doesn't allow you to reference another row from the same table without using the $(=).