Complex aggr query

[#1]

Hi Everyone,

I have following table

ID          Date           Amount

1          01/07/2012    50

1          02/07/2012     12

1          02/07/2012     13

1          05/07/2012     14

2          01/07/2012     22

2          04/07/2012     23

3          01/07/2012     32

3          03/07/2012     12

what i want is sum of amont for the max id , whose max date is less than 1 day from current date

i.e. first id 1,2 should be picked on the basis of date and then sum of amount (22+23) from max of the id's (from 1 & 2 , 2 should be picked )

[Anonymous]

Why should ID=1 be picked? It's max date is 05/07/2012 (i.e. not less than 1 day from current date)

[#3]

ok, 1 or less day i.e. current day and the previous day .

for today and yesterday id's should be picked (1,2) and  from them 2 should be picked since 2 is greater than 1 .

[#4]

Hi,

Can you also provide an output table based on your input? I don't quite get what you want.

Regards,

Xue Bin

[#5]

output should be in a textbox with value 45 (i.e. 22+23)

[Anonymous]

So you're saying that you want to sum up the amount for the max(ID) whose maximum date is not equal to today or the previous day?

[#7]

i mean max(date) should be today or yesterday ,, and from them max id

sorry for bad  explanation

[Anonymous · Accepted solution]

Ok, I'm thinking that there's a much easier way to do this but here goes:

=sum({$<ID={$(=max({$<Date={'$(=date(Today(),'DD/MM/YYYY'))','$(=Date(Today()-1,'DD/MM/YYYY'))'}>} ID))}>}Amount)