Aggregation based on maximum value of record

[#1]

Hi all,

I'm struggeling with this for a few days now so I hope one of you can help me out here.

I have a table like this:

ID	Code	Sort
1	R
1	V	1
2	R
2	E1	2
3	R
3	E2	2
3	V	1
4	R
4	E2	2
4	B	3

And I would like to have a chart in the front end counting the number of ID's by Code with only the records counted with the lowest sort value. So the result should be:

Code	Count
V	2
E1	1
E2	1
B	0
R	0

I've been using the firstsortvalue() function but as soon as I add Code as a dimension it doesn't work anymore.

All help is welcome!

Erik

[RedSky001]

How did you get a count of 1 for code E2, when the sort value is 2 for both rows?  I would of thought the count should be 2... I'm confused (doesn't take much)

[#3]

Because the E2 for ID 3 has a sort value of 2 while V (of ID 3) has a sort value of 1. So only the smallest sort value should be counted.

[RedSky001 · Accepted solution]

Create a calculated dimension

=AGGR(if(Sort = Min(TOTAL <ID>Sort),Code),ID,Code)

then an expression:

count(ID)

B & R won't appear if the related sort values are nulls...

[#5]

That's it!

Thanks a lot for your help

Erik