Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi - How to use concat and Firsortvalue in expression ? Want to get the date and type based on the min date for each ID.
Ex: Type
ID | Type | Date |
1 | Start | 02/22/22 |
1 | Process | 02/22/22 |
1 | End | 03/01/22 |
2 | Start | 02/23/22 |
2 | End | 03/02/22 |
Here for ID 1 02/22 is min date and since Start and End has the same date I want to Concat both Types and dateS.
Output would be as below
1 | Start - 02/22/22 & Process - 02/22/22 |
2 | Start - 02/23/22 |
Like this?
FirstSortedValue(DISTINCT Aggr( Concat ( Type & '-' & Date, '&'), ID, Date), Date)
Hi,
I'm not sure of the shape of your data but if you want to display the 'Start' and 'Process' types in one field with the relevant dates then you can try something like this:
Add ID as your dimension
Then the below as a measure
=only({$<Type={Start}>}Type) & '-' & date(min({$<Type={Start}>}Date)) &
if (len(only({$<Type={Process}>}Type)) > 0,
' & ' & only({$<Type={Process}>}Type) & '-' & date(min({$<Type={Process}>}Date)))
I hope this helps.
Thanks
Anthony
Like this?
FirstSortedValue(DISTINCT Aggr( Concat ( Type & '-' & Date, '&'), ID, Date), Date)
Awesome