How to display each values of Sum grouped by on Chart from descending order?
Question
Application Type
Reactive

I have this kind of Aggregate with two columns (Categorized, Item) grouped by , and the Sum.


I'm trying to display the above Aggregate List on Chart from descending order.

I mean I want to visualize Sum (AmountSum) by "Categorized" with DataSeriesName of "Item" on Chart,  but I can't and I'm stuck.

How do you make this kind of Aggregate List with Grouped by and Sum into Chart from descending order?

I wonder if you need to add something... 

Do you have any ideas? I'd appreciate it if you could give me some advice.


Please see attached (oml file ) for details.

Best regards,


yes having amount entity multiple times as source is what I mean with self-join.  You could probably make that work, but it's too complex for me to think about on a weekend...

So I would move this aggregate into a data action, and give that dataAction an output list.

In your Fetch action, you first run this aggregate, and another aggregate fetching the totals per category.

You blend the result of both into your output list, and then sort it on category totals.

see attached oml

Dorine

Chart.oml

Hi Tsubasa Yoshikawa,

Have you tried to order by the calculated column (AmountSum)?

Right click on the AmountSum column > Sort Z-A.


Hi  Inês Ponces,

Thanks.

I tried to  order by the calculated column (AmountSum) as you said, but it doesn't line up from descending order from left on Chart.

When I open in browser, it doesn't work like this.

I wonder why this is happening.

I suppose I'm not missing anything in my attached oml.

Does that mean it should be descending order by ordering by the calculated column (AmountSum) ?

Best,

 



GroupByChart.oml

I just tried sorting Column chart by SQL in Data action.

However I'm facing a weird issue.

In the two images, the above Chart is made by using Aggregate in Interface tab as Source Data PointList.

The below Chart is made by using SQL in Data action as Source Data PointList.

As you can see, in case of the below, the data of some columns(JM, AME) don't show up on chart.

I'm not sure why this is happening.


I implemented it in Data action. SQL Test was successful.

I suppose there shouldn't be any problem but where am I missing?

I updated my oml for your information. 

If you don't  mind, I'd appreciate it if you could tell me where is the problem.

I'd like to get your advice.

Best,

GroupByChart_Updated.oml

Hi Tsubasa,

You just need to remove one sorting for category , so that the list is only sorted with AmountSum.

Because currently, its getting sort by category first and when there are same categories, it will sort by AmountSum.

Hope it works.

Thanks

Ambar

That won´ t be enough, he wants probably sorted by total of all dataseries for a given label, these totals that come out of the aggregate are only at category + item level,  not at category level. 

You could try some complex self-joins + calculations inside your aggregate, but I think you´ ll be better of with some post processing of aggregate results.

Dorine

Hi Dorine and Ambar ,

Yes. What Dorine said is what I want.

(these totals that come out of the aggregate are only at category + item level,  not at category level.  )

This image is what I had in mind.  As a result, 

Label: "Category",  Value :"AmountSum2",  DataSeriesName:" Item" 

I want to display Label from on Chart in descending order of AmountSum2. 


But I can't get what Self-joins means. What exactly do I have to do?

It won't be correct to add the same Entity and process them in Add source tab.....


Sorry for my poor understanding. I can't understand what self-joins is to do.


I look forward to your reply.


yes having amount entity multiple times as source is what I mean with self-join.  You could probably make that work, but it's too complex for me to think about on a weekend...

So I would move this aggregate into a data action, and give that dataAction an output list.

In your Fetch action, you first run this aggregate, and another aggregate fetching the totals per category.

You blend the result of both into your output list, and then sort it on category totals.

see attached oml

Dorine

Chart.oml


Thanks for the correction Dorine and Tsubasa.

Thank you for telling me.

I exactly understood what you mean thanks to your attached oml.

I'd like to make use of that logic of your oml so far. I appreciate it.

But why didn't the values show up on part of label as the below image in my oml?..... I'm in doubt.

Am I right in thinking that it was not enough just to set the columns grouped by to DataPoint.. 

* This is the image I inserted in the previous post

Don´ t know, see the logic in the oml I made for you, it works there with your bootstrapped data, so you must still be doing something different

Oh, Sorry,

I understood the logic in your oml is the solution to what I want.

It worked with that logic.   

Thanks,

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.