Load Value of the highest date per month in script

[#1]

Hello,

How do i load only the values of the highest date per month.

Example:

Date               Value

01/01/2011      3000

02/01/2011      3500

14/01/2011      2500

01/02/2011      1000

02/02/2011      4500

18/02/2011      3500

I want to load this datas

14/01/2011      2500

and

18/02/2011      3500

Is it possible?

Regards,

Lukas

[Miguel_Angel_Baeyens · Accepted solution]

Lukas,

See whether the following script will do

LOAD Month(Date) AS Month, 
     FirstSortedValue(Value, -Date) AS HighestDateValue 
INLINE [
Date, Value
01/01/2011, 3000
02/01/2011, 3500
14/01/2011, 2500
01/02/2011, 1000
02/02/2011, 4500
18/02/2011, 3500
]
GROUP BY Month(Date);

Note that FirstSortedValue() is an aggregation function, and as such, you need to GROUP BY all fields that are not in it, in this case, Month. I'm using INLINE as the method of loading, it will work with a FROM or a SQL SELECT as well. The "-" before the Date field means the sort is descending (highest date). That whole script reads "return the first value of field Value ordered by the value in Date descending, grouping the Date field by month".

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica