Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try this expression:
=FirstSortedValue(Sales, Num)
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
Hi
have you tried this ?
RANGEMIN(ABOVE(SUM((Sales)),0,RowNo(TOTAL)))
or
aggr(RANGEMIN(ABOVE(SUM((Sales)),0,RowNo(TOTAL))),Category)
Wow.... I am shocked how simple it is. I have used FirstSortedValue before but didn't realize this time.
Thank you!
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)
Actually, having multiple sales value per Num, this expression is not working (null returned)
Yes!
I updated my expression, can you check if this is working:
=FirstSortedValue(Aggr(Sum(Sales), Num, Category), Aggr(Num, Num, Category))
Yes, perfectly!
Many thanks
The new updated expression worked out?