Sum Values Having Max Date

[#1]

With this data:

Date, Name, Value

1/1/12, Bob, 3

1/1/12, Bob, 4

3/1/10, Bob, 5

2/1/12, Jane, 1

2/1/12, Jane, 1

1/1/10, Jane, 5

I only need to return the sum of the values for each name, for only the max date for each name.  So in this scenario, the result would be:

1/1/12, Bob, 7

2/1/12, Jane, 2

Can someone please let me know what the max, sum, aggr formula (or any formula) for computing the correct sum's?

Thanks very much!

[#2]

With Name as a dimention, try:

sum({$<Date={'$(=max(Date))'}>} Value)

Regards!

[#3]

Thanks, thats the right idea, but this solution only returns the max of all the dates:

2/1/12, Jane, 2

in this case.

I need the max of the dates per each person. 

[#4]

Hi,

You can try using a Pivot table with the expression

sum({$<Date={'$(=max(Date))'}>} Value)

and Name as the dimension in the table.

Hope that helps.

Regards,

-Khaled.

[#5]

Thanks.  I am using a Straight Table, although a Pivot Table has the same issue.  Using this formla will return the max of ALL the dates.  I need to aggr the dates by the Name so I can sum up the Value for each name using only the max date for each Name.  

It's close but not quite there.  

[nagaiank]

Try the following which gives the result desired by you.

Temp:

load * Inline [

Date, Name, Value

1/1/12, Bob, 3

1/1/12, Bob, 4

3/1/10, Bob, 5

2/1/12, Jane, 1

2/1/12, Jane, 1

1/1/10, Jane, 5

];

Temp2:

NoConcatenate

load Date, Name, Sum(Value) as Value2 Resident Temp group by Date,Name;

Drop Table Temp;

Data:

LOAD Name

          , FirstSortedValue(Date,-Date) as MaxDate

          , FirstSortedValue(Value2,-Date) as MaxValue

          Resident Temp2 Group By Name;

Drop Table Temp2;

[#7]

If reloading is not an option, what expression can I use without reloading?

Thanks.

[flipside · Accepted solution]

Does this work? Can probably be amended to set analysis if you prefer.

=sum(Value*if(Date=aggr(nodistinct max(Date), Name),1,0))

flipside

[#9]

DING DING DING DING!!!  Thank you, perfect!   You win. 

(Please let me know if you are open to questions offline that you can bill your time for.)

Thanks much,

Richard

[flipside]

Cheers Richard, just test it thoroughly because (to be honest) I kind of stumbled on the NODISTINCT keyword solution for this one.

For small problems like this, there's plenty of help on here for free!  I don't do the whole invoicing malarky at the moment, anyway.

flipside