topic Re: How to sort a dimension by highest revenue in a filter pane? in App Development

How to sort a dimension by highest revenue using Dual?

p0cket0m — Wed, 16 Aug 2023 14:18:25 GMT

I have a dimension called BusinessDivision which is used in a filter pane.

I want to display the values by highest revenue first, those should be on top of the filter list.

I tried

=dual(BusinessDivision, rank(sum(Revenue)))

but while the formula editor says its ok, in the filter pane I get 'Invalid dimension' error.

What am I doing wrong?

Re: How to sort a dimension by highest revenue in a filter pane?

salonicdk28 — Wed, 16 Aug 2023 11:39:44 GMT

Hi,

Can you try sorting by expression in that dimension field filter pane and in expression write sum(Revenue) and then see if it works

Thanks,

Saloni

Re: How to sort a dimension by highest revenue in a filter pane?

salonicdk28 — Wed, 16 Aug 2023 11:39:55 GMT

Hi,

Can you try sorting by expression in that dimension field filter pane and in expression write sum(Revenue) and then see if it works

Thanks,

Saloni

Re: How to sort a dimension by highest revenue in a filter pane?

p0cket0m — Wed, 16 Aug 2023 14:18:01 GMT

Hi Saloni,

Thanks for your fast reply. Sorting by expression with sum formula works 😊

But how would it work with a master-dimension and using dual? That way would be better because our worksheet designers then dont need to think about it everytime they use it. It would sort always by revenue with automatic sorting.

Re: How to sort a dimension by highest revenue in a filter pane?

salonicdk28 — Wed, 16 Aug 2023 14:38:28 GMT

Use aggr in your dual expression -

=aggr(dual(BusinessDivision,rank(sum(Revenue)),BusinessDivision)

Re: How to sort a dimension by highest revenue in a filter pane?

p0cket0m — Thu, 17 Aug 2023 11:02:13 GMT

This is exactly what I was looking for.

Thanks!