Rank or counter in a chart with 2 sorting dimensions...

[#1]

Hi:

We've a problem using rowno(TOTAL) when sorting by 2 dimensions.

We've a chart with the sales to our costumers with 2 dimensions; sum(sales) and count(costumer) to get the total amount of sales and the number of tickets.

As you can see, we can sort the char firstly descending by number of tickets and then ascending by total of sales without any problem.

However, our users need a counter so we've tried to use rowno(TOTAL) function.

When we add this new field, as you can see in the chart, suddenly, we loose sorting...

What function could we use to make a ranking or counter using two ordering dimensions?.

Thanks.

[ToniKautto · Accepted solution]

As you can see in the manual for the inter record function RowNo(), sorting straight tables by expression is not allowed when you use a chart inter record functions. Therefore you can not arrange the sorting directly by arranging the sort order in the chart's expressions.

The easiest workaround for this scenario is that you simply sort the chart by the first dimension instead. The trick is then to sort the dimension by an expression. Assuming that you want to sort the count() first and then the sum(), you would set up the sort expression as below to simply make the count() result the most significant part;

=count(F1)*1000000 + sum(F4)

NOTE: The multiplier needs to be larger than the largest sum for this to work properly

[#3]

Thank you very much!!!!

We've solved the problem perfectly!!!