7 Replies Latest reply: Oct 4, 2013 5:49 AM by Fabrice Aunez

# Use sorted expression value for other expression

Hi all

I have an expression in a straight table, Expression1, which calculates a specific value. Expression2 must use the values of Expression1, but sorted ascending.

Expression1Expression2
0.875890.28185
0.281850.28185
0.281850.87589

How can I do this?

Thanks!

Kind regards

• ###### Re: Use sorted expression value for other expression

One way of doing is in the straight table, double click on Expression 1 column to sort it in ascending or descending order.

Not sure if there any other way to achieve this.

• ###### Re: Use sorted expression value for other expression

I can't do this because Expression3 will use Expression1 and Expression2.

• ###### Re: Use sorted expression value for other expression

Hi,

I have perhaps something for you:

we will combine the below() function (to take data below) and the number of rows.

The row you want to take is sth like:  NoOfRows()- 2*rowno()+1

Where NoofRows() returns the number of rows of your table

rowno() returns the current row number

If i encapsulate everything: = below(sum([Sales]), NoOfRows()- 2*rowno()+1)

The below() function accepts negative numbers : it will return above numbers in that case.

Fabrice

• ###### Re: Use sorted expression value for other expression

Thank you for your answer, but I think my question was not clear.

The data can also be like this:

Expression1               Expression2

3                                   1

1                                   1

5                                   3

4                                   4

1                                   5

Kind regards

• ###### Re: Use sorted expression value for other expression

Britt,

Well easy No I am kidding ! 30 minuts I am trying to get it ....

subfield (concat(total aggr(sum([Ventes Unite]), FABRICANT_LName) , '|', -aggr(sum([Ventes Unite]), FABRICANT_LName)), '|', rowno())

where : FABRICANT_LNAME is my dimension of the chart and [Ventes Unite] my expression

In fact, I do a concatenation of the Expression in the tab : aggr(sum(expression), DimOftheTab)). The last parameter of concat is the sort order: I want highest values first, therefore I inverse it :  -aggr(....)

Once this concatenation done, I just use subfield that extract the Nth field. where N is the number of the line returned by rowno()

Fabrice

• ###### Re: Use sorted expression value for other expression

Thank you very much, Fabrice! It works!

Kind regards

Britt

• ###### Re: Use sorted expression value for other expression

Excellent, you are wellcome

Perhaps you will have to use the num() function if you need to format the number ...

Fabrice