Need a special offer?Find out if your project fits.
+

custom sort by the result of aggregation

Answered
dan asked on May 18, 2020

Hi there
Is it possible to apply custom sorting by the result of an aggregation? eg order by absolute(sum(measure)), something like that?
We'd like to see the aggregated numbers' real values (ie, can be positive or negative), but the ordering should be by the absolute value only.
Dan

9 answers

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster May 19, 2020

Hello, Dan,
 
Thank you for your question.
 
In case your requirement is to sort members basing on absolute values but display actual values, our team suggests checking out the following approaches.
The most simple option is to create the calculated value that would represent the absolute value (abs) and apply sorting on it.
We have prepared an example demonstrating such an approach.
 
In case there is no possibility of keeping such a column on the grid, we recommend paying attention to the sortingMethod API call. It sets custom sorting for hierarchy members. Therefore, it is possible to pass the expected order and sort the hierarchy manually.
Please find out an example demonstrating the described approach.
 
Our team would like to provide some additional explanations about the following code snippet used in the example:

let sortOrder = ["B", "C", "A", "D"];

flexmonster.sortingMethod("Member", function(a, b) {
  console.log(sortOrder.indexOf(a));
  return sortOrder.indexOf(a) > sortOrder.indexOf(b) ? 1 : -1;
});

 
As can be seen, the order itself is stored in the array. Next, the sortingMethod uses custom logic in order to sort members of the "Member" hierarchy.
 
We hope it works well for your case.
In case your requirement differs from the one described above, we want to ask you to provide some additional explanations about the result you want to achieve.
 
Kind regards,
Illia

Public
dan May 20, 2020

Hi Illia
The requirement is to sort descendingly on the absolute value of the aggregated measure. So in the 2nd Fiddle example above, that means sorting on the absolute value of  "Total sum of Value", and the expected order is [-101,90,79,-3]. I changed to use '...sortingMethod("Value", ...' in the example and can see that it was not applied. It seems sortingMethod only applies to the headers but not the measures.

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster May 20, 2020

Hello, Dan,
 
Thank you for writing to us.
 
You are right about the fact that the sortingMehtod can be applied to members only and does not affect measures. That is why the desired order needs to be passed or calculated using custom logic and not calculated by Flexmonster. In the example, the required order is represented as an array of members' names. Its position in the array decides the order needed to be displayed on the grid.
 
In case passing the order or calculating it manually is not an option for your case, we suggest using the first approach. The created calculated value may be hidden using the tableSizes property of the report object. Please see an example we have prepared for you.
Detailed information about the tableSizes property can be found in our documentation.
 
We hope it works for your case.
Please contact us in case additional questions occur.
 
Best regards,
Illia

Public
dan May 21, 2020

Thanks for the example, that works when the page is first loaded. However if the column header is clicked to sort, it falls back to the wrong sorting. Also the column header did not show that it's sorted on first load. Is there a way to control both these aspects?

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster May 21, 2020

Hello, Dan,
 
Thank you for your feedback.
 
Our team would like to kindly explain that sorting is not applied to the column visible on the grid. It is applied to the hidden calculated value. Therefore, in case sorting of the visible column is changed, it overwrites the original sorting and breaks the desired order.
The same reason leads to the absence of the icon indicating that the column is sorted.
 
Such behavior cannot be changed due to specific of the used approach.
 
Please contact us in case of additional questions.
 
Kind regards,
Illia

Public
dan May 22, 2020

Hi Illia
Is there a way to customize the onclick event on the measure header cell?
 
regards
Dan

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster May 22, 2020

Hello,
 
In case the mentioned event is going to be used to sort the created calculated value, we want to notice that sorting is going to display the hidden column again. Our team would like to kindly explain that the approaches provided earlier are the best we can offer for your case.
 
If the use case where the mentioned event needs to be used differs from the described one, please provide us with additional details.
 
We are looking forward to hearing from you.
 
Kind regards,
Illia

Public
Catalin Bratu October 8, 2020

Hello,
I am too interested in a solution for a similar problem like Dan's one. Is it too dificult to be able to specify a custom sort function, like the one you can provide in order to sort the categories?
Something like this:
flexmonster.sortingMethodForMeasure("measure_name", function (a, b) {
      return abs(a) < abs(b) ? -1 : 1;
  });
 
Thanks,
Catalin
 

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster October 9, 2020

Hello, Catalin,
 
Thank you for reaching out to us.
 
Our team wants to explain that all known workarounds for measures custom sorting are described within this thread.
Currently, Flexmonster does not provide the built-in feature to apply custom sorting to measures.
 
Feel free to contact us in case any other questions arise.
 
Best regards,
Illia

Please login or Register to Submit Answer