Forum Discussion
Advocate IIError using {[Index]-1} to reference field in previous row
Hi
I'm trying to reference a field in the previous row using {[Index]-1} but I get the error:
"We cannot convert the value to type list".
Please see the screen-shots below that explain what I'm doing.
Your assistance will be greatly appreciated.
Thanks, Mark.
(1) The code with the {[Index]-1}
(2) The "Index" column.
(3) The errors I get in the column.
(4) The error description.
In the first formula you are refering to the field [Margin], which refers to the value in the current row, but you want to refer to the entire table column, so [Margin] must be preceded by the previous step name.
Otherwise, this kind of references tend to be very inefficient, so if you have a large table, the refresh will take "forever".
A far better solution is to get the data from the previous row on the current row, as I explained in this topic.
3 Replies
- MarcelBeug
Community Champion
In the first formula you are refering to the field [Margin], which refers to the value in the current row, but you want to refer to the entire table column, so [Margin] must be preceded by the previous step name.
Otherwise, this kind of references tend to be very inefficient, so if you have a large table, the refresh will take "forever".
A far better solution is to get the data from the previous row on the current row, as I explained in this topic.
SkemazMany thanks for your support.
What I want to do is refer to the previous row in order to create a cumulative total up to the point when I hit a null value. I was just attempting to figure out how to refer to the previous row data. I'm away from my computer at the moment and will look at your suggestion in detail when I can.
Thanks for assisting.
Regards, Mark.- Skemaz
Hi Marcel
Thanks for pointing me in the right direction and taking me away from going down the path of using {[Index]-1}.
I tried out that option ans as you pointed out, it results in a very long query execution time (I just killed the process after it had taken way to long already).
I actually already had the 2 indexes and had done the merge, so thinking about it I used groupings to solve my problem of summing the [Margin] column.
Many thanks for assisting and best regards, Mark.