Show a specific date with reference

[PeterHa]

Hello,

I am trying to get the date by a reference from a specific status.

Reference	Status	date
1	order	21-1-2021
2	order	17-5-2021
1	sold	11-3-2022
3	order	15-1-2021
1	cash	15-4-2022
2	sold	15-3-2022
3	sold	18-7-2022

I would like to have the specific date per reference by the status sold.

The outcome should be like this.

reference	date
1	11-3-2022
2	15-3-2022
3	18-7-2022

i tried with the date with:

if(status='sold',date)

But that doesn't work correctly for me.

Does anyone have a solution?

[stevejoyce]

You want this in a table expression?

You have to decide what aggregation function you want to use on date if there is possibility of multiple sold dates per ref id, otherwise it doesn't matter if only 1.

reference as table dimension

expression:  only{<status={'sold'} >} date)

[PeterHa]

Thanks Stevejoyce,

The status only accures 1 time per reference, so this works perfectly.