Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have a table that show my products
PRODUCT | DATE | TOTAL |
A | 01/02/2018 | 300 |
A | 05/02/2019 | 140 |
B | 01/02/2015 | 200 |
C | 08/03/2019 | 500 |
I would like to show just the max date that we bought the product "A", same this:
PRODUCT | DATE | TOTAL |
A | 05/02/2019 | 140 |
B | 01/02/2015 | 200 |
C | 08/03/2019 | 500 |
Why not just get max date from an expression, like:
Dimension : PRODUCT
Exp1: Date(Max(DATE))
Exp2: FirstSortedValue( TOTAL, -DATE)
Note: If you have multiple transactions for max dates, you might have to use sum() along with aggr() within firstsortedvalue().
Hey Eliosa,
Here's the code that'll do that for you.
Data: // Entry Dataset
LOAD
PRODUCT,
TOTAL,
Date(Date#(DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') as DATE //Here we make sure the dateformat is clearly identified
;
LOAD * INLINE [
PRODUCT, DATE, TOTAL
A, 01/02/2018, 300
A, 05/02/2019, 140
B, 01/02/2015, 200
C, 08/03/2019, 500
];
INNER JOIN(Data) //We filter the input table to only the Max Date values per Product
LOAD
PRODUCT,
Date(Max(DATE), 'DD/MM/YYYY') as DATE
RESIDENT Data
GROUP BY
PRODUCT
;
I hope that helps!
Kind regards,
S.T.
Why not just get max date from an expression, like:
Dimension : PRODUCT
Exp1: Date(Max(DATE))
Exp2: FirstSortedValue( TOTAL, -DATE)
Note: If you have multiple transactions for max dates, you might have to use sum() along with aggr() within firstsortedvalue().