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

[#1]

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.

Thanks in advance

Shivesh

[sunny_talwar]

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

[#3]

Hi Sunny,

Example.xlsx - Google Drive

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

[sunny_talwar]

May be this:

Dimension

USER ID

Expression

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

[jpenuliar]

You can use FirstSortedValue function

firstsortedvalue ([ distinct ] expression [, sort-weight [, n ]])

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:

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]

FROM

[..\Downloads\Example.xlsx]

(ooxml, embedded labels, table is Sheet1)

;

[sunny_talwar]

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

[jpenuliar]

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‌!

[sunny_talwar]

Merry Christmas and Happy New to you too my friend