Max(Date) vs. Set analysis?

[Anonymous]

Hi,

I have the following problem and I have you know the answer:

In a fact table I have, for example, a few records.

CustomerNr	PolisNr	BookDate	Amount
1234	abc-1	1-1-2015	1000
1234	abc-1	9-1-2015	950
1234	abc-1	6-6-2015	750
1234	def-1	1-9-2015	500
5678	dfg-1	6-3-2015	700
5678	dfg-1	15-9-2015	1250
5678	dfg-1	16-9-2015	850
5678	dfg-1	1-11-2015	350

Bases on Time selections I want the correct Amount per CustomerNr and PolisNr.

For example:

When I selecting year 2015 i want to see an Amount of 750 + 500 + 350 = 1600

When I selecting year 2015 and Quarter 1, I want to see 950

When I selecting year 2015 and month 9, I want to see 500 + 850

When I selecting year 2015 and week 38, I want to see 850

When I selecting year 2015 and day 1-9-2015, I want to see 500

I hope you can give me an answer!

Best regards,

Arjan

[swuehl · Accepted solution]

Try

=Sum( Aggr( FirstSortedValue(Amount, -BookDate), CustomerNr, PolisNr))

[martijn]

That what Swuehl says

[Anonymous]

Thanks Swuehl!