ifelse(
{child} = 'Increase (Decrease) in common stocks',
({Changes_CY} + ({Profit LY}) * -1),
,
{Changes_CY}
)
My problem is Profit LY is value located in the first row only, “Profit for the period”. And as you see in the formula, the row with child ‘Increase (Decrease) in common stocks’ needs to take this value to use it in it’s formula.
My question is how to use value from the row in the top in a calculation for a row in the middle or bottom.
Hello @haythoo, welcome to the QuickSight community! The child field you are referencing, is it created by a calculated field or is a value being returned from the dataset you are importing?
It seems like you are close to the right solution but in that ifelse you described above, is the string of ‘Increase (Decrease) in common stocks’ the name of your field or is it a specific value returned from a field?
Hello @haythoo, so I think the way to solve this issue, is you will need to make the value for Profit LY accessible on every row of your dataset. If that value does not exist within the same table row in your dataset as the row containing ‘Increase (Decrease) in common stocks’, you will not be able to aggregate them.
If you add a calculation in your SQL when ingesting the dataset into QuickSight to make Profit LY a column that will import the value on every row, the function you mentioned above should work as expected.
