Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Can you elaborate on your requirement by sharing some sample data or sample application?
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
May be this:
Dimension
USER ID
Expression
Date(Min([Date of Transaction], 4))
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)
;
But jpenuliar, why not a simple Max? Why complicate things by using FirstSortedValue?
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!
Merry Christmas and Happy New to you too my friend