Show max date in dimension

[eloisateixeira]

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

[Stoyan_Terziev]

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.

[tresesco · Accepted solution]

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().