Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I have a problem aggregating a value from my data.
I have found several forum posts but for some reason they dont seem to work for me.
I guess i do something wrong.
Im breaking my problem down to a simple example.
This is my data:
Columns: ID, TABLENAME, NUMBER_ARTICLES
15, 'Warehouse1', 563
13, 'Warehouse1', 60
11, 'Warehouse2', 1500
10, 'Warehouse2', 12
Per warehouse I would now like to get the number of articles of the row with the highest id.
So basically this:
- 15, 'Warehouse1', 563
- 11, 'Warehouse2', 1500
I tried it with set analysis but i cant get it to work.
This is the formular for the number of articles.
On the other axis i am displaying the warehouse.
max
(
{
$<
[ID]={'$(=max( [ID] ))'}
>
}
[NUMBER_ARTICLES]
)
Can somebody tell me what i am doing wrong?
Try this:
aggr(FirstSortedValue(ID,-NUMBER_ARTICLES),TABLENAME)
result :
hi
you can't use set analysis in your case
because set analysis isn't calculated on raw basis bot on the whole data set
so max id will only bring back one value in your case 15
you can use this function instead
FirstSortedValue(NUMBER_ARTICLES,-ID)
Try this:
aggr(FirstSortedValue(ID,-NUMBER_ARTICLES),TABLENAME)
result :
The parameters are switched but that's exatcly what i needed
Thanks a lot!