Use of aggregations within a dimension

[luis_pimentel]

Hi all,

I have the following scenario:

Category	Num	Sales
A	7	102
A	9	101
A	10	103
A	16	100
B	6	105
B	8	106
B	15	104
B	20	107
C	25	108
C	27	109
C	28	110
C	30	111
D	5	120
D	12	115
D	13	116
D	23	114
D	45	113

And I am trying to get the following result:

Category	Sales of MIN(Num)	Other Expressions	Othe Expressions
A	102
B	105
C	108
D	120

Which is the Sum of Sales for each category when Num is the minimum for that category.

I have tried all possible aggregations by Category and Set Analysis. None worked.

Note:

Having the Num field as a dimension is not an option. In addition, it is a must that the solution allows other expressions besides the one I am requesting help for.

The following expression works fine having Num as dimension, "Show zero values" checked and with only one expression in the chart, but as said that is not suitable for my scenario:

IF ( Num = AGGR(NODISTINCT MIN(Num), Category) ,SUM(Sales) )

Any ideas?

Thank you.

[sunny_talwar · Accepted solution]

Try this expression:

=FirstSortedValue(Sales, Num)

[sunny_talwar]

Or this:

=FirstSortedValue(Aggr(Sum(Sales), Num, Category), Aggr(Num, Num, Category))

UPDATE: If you can have multiple sales value per Num.

UPDATE2: Further updated the expression to make sure it gives the correct output in all cases

[Anonymous]

Hi

have you tried this ?

RANGEMIN(ABOVE(SUM((Sales)),0,RowNo(TOTAL))) 

or

aggr(RANGEMIN(ABOVE(SUM((Sales)),0,RowNo(TOTAL))),Category)

[luis_pimentel]

Wow.... I am shocked how simple it is. I have used FirstSortedValue before but didn't realize this time.

Thank you!

[sunny_talwar]

May be look here to learn when you might need to use FirstSortedValue

Value Associated with Min/Max Value of Another Field (Front End Solution)

[luis_pimentel]

Actually, having multiple sales value per Num, this expression is not working (null returned)

Yes!

[sunny_talwar]

I updated my expression, can you check if this is working:

=FirstSortedValue(Aggr(Sum(Sales), Num, Category), Aggr(Num, Num, Category))

[luis_pimentel]

Yes, perfectly!

Many thanks

[sunny_talwar]

The new updated expression worked out?