topic Re: Show max date in dimension in QlikView

Show max date in dimension

eloisateixeira — Fri, 07 Feb 2020 12:24:10 GMT

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

Re: Show max date in dimension

Stoyan_Terziev — Fri, 07 Feb 2020 12:56:03 GMT

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.

Re: Show max date in dimension

tresB — Fri, 07 Feb 2020 13:01:13 GMT

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