# Qlik Sense App Development

Not applicable

## Create a measure/dimension to calculate 10th date of transaction for a particular user id in a table.

Hello everyone,

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.

Shivesh

Tags (2)
7 Replies
MVP

## Re: Create a measure/dimension to calculate 10th date of transaction for a particular user id in a table.

Can you elaborate on your requirement by sharing some sample data or sample application?

Not applicable

## Re: Create a measure/dimension to calculate 10th date of transaction for a particular user id in a table.

Hi Sunny,

Please refer this. It has different user ids and their different transaction dates, Need help in finding 4th transaction date of every user id.

Thanks

Shivesh

MVP

## Re: Create a measure/dimension to calculate 10th date of transaction for a particular user id in a table.

May be this:

Dimension

USER ID

Expression

Date(Min([Date of Transaction], 4))

Valued Contributor III

## Re: Create a measure/dimension to calculate 10th date of transaction for a particular user id in a table.

You can use FirstSortedValue function

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.

see below:

firstsortedvalue([Date of transaction], [Date of transaction],4) as [Date of transaction]

group by [USER ID]

;

Date([Date of transaction]) as [Date of transaction]

FROM

(ooxml, embedded labels, table is Sheet1)

;

MVP

## Re: Create a measure/dimension to calculate 10th date of transaction for a particular user id in a table.

But jpenuliar‌, why not a simple Max? Why complicate things by using FirstSortedValue?

Valued Contributor III

## Re: Create a measure/dimension to calculate 10th date of transaction for a particular user id in a table.

tbh, Firstsortedvalue came to mind first.

Plus i thought if user wants to see 10th position, what are the chances of looking for nth position afterwards?

so i came up with the firstsortedvalue solution.

now that you mentioned it, i could probably look at coming up other solutions (like youe mentioned), justto keep it more challenging for us.

Merry Christmas sunny t‌!

MVP

## Re: Create a measure/dimension to calculate 10th date of transaction for a particular user id in a table.

Merry Christmas and Happy New to you too my friend