Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
how to show Transaction ID based on Max Payment Date.
Orginal Data:
Customer ID | Transaction No | Payment Date |
csx01234d | snzcret34 | 7/15/2016 |
csx01234d | ynzcret35 | 7/16/2016 |
csx01234d | knzcre136 | 7/17/2016 |
csx23495 | refrgmp532 | 7/18/2016 |
csx23495 | tefrgmp533 | 7/19/2016 |
csx23495 | hefrgmp534 | 7/20/2016 |
csx23458 | kloiedftp13 | 7/21/2016 |
csx29458 | ploiedftp14 | 7/22/2016 |
csx29458 | zloiedftp15 | 7/23/2016 |
csx29460 | lopredts24 | 7/24/2016 |
csx29460 | fopredts25 | 7/25/2016 |
csx38570 | sodbdu56 | 7/26/2016 |
csx38570 | nodbdu57 | 7/27/2016 |
csx38570 | modbdu58 | 7/28/2016 |
Output
Customer ID | Transaction No | Max Payment Date |
csx01234d | knzcre136 | 7/17/2016 |
csx23495 | hefrgmp534 | 7/20/2016 |
csx29458 | zloiedftp15 | 7/23/2016 |
csx29460 | fopredts25 | 7/25/2016 |
csx38570 | modbdu58 | 7/28/2016 |
Hi,
Data:
LOAD * INLINE [
Customer ID, Transaction No, Payment Date
csx01234d, snzcret34, 7/15/2016
csx01234d, ynzcret35, 7/16/2016
csx01234d, knzcre136, 7/17/2016
csx23495, refrgmp532, 7/18/2016
csx23495, tefrgmp533, 7/19/2016
csx23495, hefrgmp534, 7/20/2016
csx23458, kloiedftp13, 7/21/2016
csx29458, ploiedftp14, 7/22/2016
csx29458, zloiedftp15, 7/23/2016
csx29460, lopredts24, 7/24/2016
csx29460, fopredts25, 7/25/2016
csx38570, sodbdu56, 7/26/2016
csx38570, nodbdu57, 7/27/2016
csx38570, modbdu58, 7/28/2016
];
Inner Join (Data)
LOAD [Customer ID],
Max([Payment Date]) as [Payment Date]
Resident Data Group by [Customer ID];
Note: Updated my solution.
Straight table:
Dimension : [Customer ID]
Expression 1 : FirstSortedValue([Transaction No], - [Payment Date])
Expression 2 : Date(Max([Payment Date]))
May be like this
FirstSortedValue([Payment Date], - [Payment Date]) would be an overkill when the same can come directly from Max([Payment Date]), I guess.
Agree, Thanks for correcting!
My Solution:
Straight table:
Dimension 1: [Customer ID]
Dimension 2 : [Transaction No]
Expression 1 : aggr(Max([Payment Date]),[Customer ID])
Please Try like this in chart
=max({1}[Payment Date])
=FirstSortedValue({1}TOTAL <[Customer ID]> [Transaction No],-[Payment Date])
In first sorted value, please note 'Minus Sign'
Thanks,
From Solomon Jeyaseelan