topic Re: Use sorted expression value for other expression in QlikView

Use sorted expression value for other expression

Fri, 04 Oct 2013 07:41:01 GMT

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.

Expression1	Expression2
0.87589	0.28185
0.28185	0.28185
0.28185	0.87589

How can I do this?

Thanks!

Kind regards

Re: Use sorted expression value for other expression

Fri, 04 Oct 2013 07:52:18 GMT

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

Fri, 04 Oct 2013 07:57:28 GMT

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

Re: Use sorted expression value for other expression

Fri, 04 Oct 2013 08:03:33 GMT

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

Fri, 04 Oct 2013 08:15:57 GMT

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

Fri, 04 Oct 2013 09:12:00 GMT

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

Fri, 04 Oct 2013 09:26:19 GMT

Thank you very much, Fabrice! It works!

Kind regards

Britt

Re: Use sorted expression value for other expression

Fri, 04 Oct 2013 09:49:51 GMT

Excellent, you are wellcome

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

Fabrice