Create a measure/dimension to calculate 10th date of transaction for a particular user id in a table.
I have a table having user ids in column 1 and their transaction date in column 2. Now I want to create a formula to find 10th transaction date of each user id. I am able to find the 1st and last dates but not in between them.
Returns the first value of expression sorted by corresponding sort-weight when expression is iterated over a number of records as defined by a group by clause. Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return NULL. By stating an n larger than 1, the nth value in order will be returned. If the word distinct occurs before the expression, all duplicates will be disregarded.
Load [USER ID],
firstsortedvalue([Date of transaction], [Date of transaction],4) as [Date of transaction]
group by [USER ID]
LOAD [USER ID],
Date([Date of transaction]) as [Date of transaction]