Create a stacking bar chart from the aggregate (reactive web)
Application Type
Reactive

Hello,

I am struggling to set up a stacking bar chart. Here is the data table which I would need to visualize the data to the stacking bar chart.


Here is my bar chart setting

My problem is that if the table is not structured like this (each month has to have enough products A,B,C,D), 

The chart would not be able to understand it correctly. For example, if I delete the 3rd row of the table:

Here is the result chart:

In "March" bar, the value of product C (90) is not correct because I already deleted this value in March. This data is actually belonged to April (product C) as in the data table => it seems to be that because I deleted the product C in March so it take over the value of product C in April to visualize the stacking bar of March.

Do you know  what is the root cause and how to fix this issue? I really need your help.

Sorry because I was not able to attached the .oml file but I attached the data excel file so that you can also try.

Thank you so much for your interesting.

Best regards,

Nguyen Phuoc Luc

New Microsoft Excel Worksheet.xlsx

Hello Luc Nguyen,

I took your data and tried out a few things. 

Instead of directly assigning the values to the graph I created my own DataPoint LIst to feed the graph.

I'm going to attach an oml with what I hope will be a solution, it's not the best performance-wise but I think it will work. 

Hope it helps! 

Paulo Rosário


BarChart.oml

Thank you so much Paulo, but it seems that I can not use your oml file because I did not have enough permision to some of the entity in the module '(System)'

Hello Paulo,

I have copied your code to my apps and it worked fine. So I would like to mark as solution.

Thank you very much.

Best regards,

Phuoc Luc.

Hi,

I would like to see your datamodel to understand what would be possible in an aggregate or SQL.  

But easiest solution that will always work is, after retrieving your data, preferably in the correct sort order, just going through it, and building an new list where you fill in the gaps.  You can see an example of that in my reply to that other post.  

So if we call what you have now your 'IncompleteList' 

  • create a distinct list of your DevelopmentAreas present in 'IncompleteList'
  • create a distinct list of your Processing statusses present in 'IncompleteList'
  • for each combination of DevelopmentArea and Processing status 
    • you do a listAppend in another list 'CompleteList'
    • you do a listfilter in 'IncompleteList' to see if that combination is there
    • if so, you fill the value in 'CompleteList', otherwise value in 'CompleteList' stays 0

The 'CompleteList' can now be used as source for your chart

Dorine

Hello Luc Nguyen,

I took your data and tried out a few things. 

Instead of directly assigning the values to the graph I created my own DataPoint LIst to feed the graph.

I'm going to attach an oml with what I hope will be a solution, it's not the best performance-wise but I think it will work. 

Hope it helps! 

Paulo Rosário


BarChart.oml

Thank you so much Paulo, but it seems that I can not use your oml file because I did not have enough permision to some of the entity in the module '(System)'

Hello Paulo,

I have copied your code to my apps and it worked fine. So I would like to mark as solution.

Thank you very much.

Best regards,

Phuoc Luc.

Hello Luc Nguyen ,

I am not sure that, if the above comment is help you or not.
But from my prospective it will not work. For the data series you need to give the same size, same order and if its not possible then you need to put as zero in database to manage same size

Example: if you have 4 product A, b, c, d
for march A=90,d=50 in your database so that time in database you need to pass B=0 and C=0 as well so you need to assign the value to every series if nothing is there See the similar post with answer (for every data series you need to pass some value that time only the chart will work fine if nothing is there keep zero)

https://www.outsystems.com/forums/discussion/41294/bar-chart-not-displaying-labels-in-the-right-order/

hope this will helps you 

Thanks and Regards,

Akshay Deshpande

Hello Akshay,

Thank you for your answer but I believe manually add value "0" for those label and dataseries is not a good solution for me. I believe that I am looking for some smart SQL as @Dorine Boudry  mentioned below that could read my database or aggregate and automatically add the datapoint which have value "0". However the issue for me is that I had not much experiences with SQL so I really hope that someone can share me some logic or action, SQL command could help to do that.

But anyway, thank you so much Akshay,

Best regards,

Phuoc Luc

Hello luc,

I am not saying used that only I want to say that it's not possible to map if you have mismatch data series 

The one way is you add zero when there is not data present 

So I will give hint there are many ways 

Add in aggregate or pass second one pass all the data to aggregate and check the all data series is covered or not if covered than okay else add in local variable the zero value and that local variable pass as source to map

SQL is also one Alternative option 

As your question asking for mismatching the data series so I posted as first post on that bases

Thanks and regards,

Akshay Deshpande

Hello Akshay,

Thank you so much for your answer. I am so sorry because my limited competence so I can not realize your hint in outsystems.

 Can you make just a short example for me with the data table in the attached excel file.

I want to make the chart with the data point is mapped from the aggregate as below figure

Here is the set up for the chart 

But with the aggregate like that (not same size, same order) , each development area do not have enough processing status (A, D1, D2, D3, D4, D5, D6, D7, D8) so the chart will not be correct.

Thank you very much for your great support as always Akshay!

Best regards,

Phuoc Luc

ChartTest.xlsx

I have one more quick question your series is dynamic or static means 

status(A, D1, D2, D3, D4, D5, D6, D7, D8) or this is dynamic (changeable) ?


Im not sure I understand static or dynamic in your question correctly. I guess it is dynamic but you can verify my below description.

We always had fixed 9 status(A, D1, D2, D3, D4, D5, D6, D7, D8) for a processing report and the report had only 1 status at a time.  For example, when starting a report, it will have  the status A and it will change to D1, D2,...,D8 by time (depending on their progress) but anyway, at any certain time it will have only 1 status.

Hope you can understood my description. Thank you so much.

Hi Luc,

the OML of Paulo isn't going to solve anything, the problem is the one Akshay is referring to, the chart widget only works if for every datapoint, the complete set of series is present in the data.

This is a post of a couple of months ago about this problem, @Benjith Sam is saying there that it is an issue with the latest version of charts component.

I don't think however, that adding dummy data in your database to make some chart work is a proper solution !

So you'll have to use some smart aggregate/sql or do some post processing of your retrieved date to insert the missing data points OR alternatively you revert back to an earlier version of the charts widget and see if that works for you.

Dorine

Hello Dorine,

Thank you so much for your answer. you understood correctly my problem. I am so sorry because my limited competence so I can not find the solution for the smart aggregate/sql as you mentioned but I really need your help for this point.

 Can you make just a short example for me with the data table in the attached excel file.

I want to make the chart with the data point is mapped from the aggregate as below figure

Here is the set up for the chart 

But with the aggregate like that (not same size, same order) , each development area do not have enough processing status (A, D1, D2, D3, D4, D5, D6, D7, D8) so the chart will not be correct.

Thank you very much  and have a nice day ahead Dorine!

Best regards,

Phuoc Luc

ChartTest.xlsx

Hi,

I would like to see your datamodel to understand what would be possible in an aggregate or SQL.  

But easiest solution that will always work is, after retrieving your data, preferably in the correct sort order, just going through it, and building an new list where you fill in the gaps.  You can see an example of that in my reply to that other post.  

So if we call what you have now your 'IncompleteList' 

  • create a distinct list of your DevelopmentAreas present in 'IncompleteList'
  • create a distinct list of your Processing statusses present in 'IncompleteList'
  • for each combination of DevelopmentArea and Processing status 
    • you do a listAppend in another list 'CompleteList'
    • you do a listfilter in 'IncompleteList' to see if that combination is there
    • if so, you fill the value in 'CompleteList', otherwise value in 'CompleteList' stays 0

The 'CompleteList' can now be used as source for your chart

Dorine

Hello Dorine, 

Thank you so much for your instruction. I have already solved my issue after referring your hint.

I would like to mark your reply as solution. 

However, I think that @Paulo Rosário 's answer is also correct for my specific case (in my question). I did copy his code to my apps and I worked fine. So,I will also mark his answer as solution.

Anyway, thank you very much for your enthusiastic support as always .

Best regards,

Phuoc Luc

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