Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
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 $(=).
row7: sum({<row_no = {'row6', 'row3'}>} bucket1) - sum({<row_no = {'row6', 'row3'}>} bucket2)
row8: rangeavg(above(sum({<row_no = {'row7'}>} bucket1 - bucket2), 0, RowNo()))
@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.
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 $(=).