If you want to create a dimension you can either use a plain field or a calculcated dimension based on a field, e.g.
=FIELD
=if(FIELD > 10, FIELD)
For more complex tasks, you can use advanced aggregation, which essentially produces again a one dimensional vector as dimension, one value per combination of advanced aggregation dimension values.
[Another, different option to create a dimension is a synthetic dimension using Valuelist() / Valueloop]
You want to implement something more complex than just a (filtered) FIELD, so let's start with advanced aggregation:
aggr( "EXP", "DIMs")
Your DIMs should be ID, since you want a value per ID.
aggr( "EXP", ID)
At this point, I sometimes create a straight table chart with dimension ID (in this case) and play around with the expression until I get what I want. We want "last approver in the sequence", and if I read something like this, I go for the FirstSortedValue() function.
Sequence mean sorted by Order per ID, and approver equals Name, so maybe something like
=FirstSortedValue( Name, -Order)
This will not handle the multiple approvers with same order number correctly, so we need to add an aggregation (and an aggregation within an aggregation like FirstSortedValue requires advanced aggregation again:
=FirstSortedValue( aggr(concat(Name,', '), ID, Order), -aggr( Order, ID, Order) )
and putting all together in one expression for the calculated dimension:
=Aggr(FirstSortedValue( aggr(concat(Name,', '), ID, Order), -aggr( Order, ID, Order)),ID )
Regards,
Stefan
