Skip to main content
Solved

Monthly cumulative line or bar chart for non financial/GL information


Forum|alt.badge.img

Use-case: Plot using a line or bar chart widget on a dashboard the monthly cumulative total amounts for won opportunities (Acumatica CRM), e.g Jan through Dec, 2023.  Anyone have an example GI configuration illustrating how to do this?

Best answer by ejmillar

Hi John,

 

I’ve found a solution to create a cumulative total.

However, Acumatica will need to be updated to 2024 R1.

The 2024 R1 update allows the ability to create a GI from another GI and I’ve used this technique to create a cumulative total for the opportunities.

The first step is to create a simple GI called ‘MonthlyOpportunityTotal’ that aggregates each month's opportunity amount.

Data sources:

CROpportunity

DateInfo

 

Relations

DateInfo.date Inner Join CROpportunity.closeDate

 

Conditions

CROpportunity.Status = ‘Won’

CROpportunity.CloseDateYear = 2014 (I’m using the sales demo system and 2014 has data)

 

Grouping

DateInfo.Month

 

Sort Order

DateInfo.Month

 

Result Grid

DateInfo.Month AS OppMonth

CROpportunity.Amount SUM Aggregation AS MonthlyTotal

 

The results should look like the below:

 

In the second part, a new GI needs to be created, which pulls in the GI above to calculate the cumulative total.

 

In Acumatica 2024 R1, we can now add a new GI as a datasource to a GI. 

Datasource

MonthlyOpportunityTotal Alias MonthlyOpportunityTotal

MonthlyOpportunityTotal Alias MonthlyOpportunityTotal2

 

Relations

MonthlyOpportunityTotal.DateInfo_Month Is Greater Than or Equal To MonthlyOpportunityTotal2.DateInfo_Month

 

Grouping

MonthlyOpportunityTotal.OppMonth

MonthlyOpportunityTotal.MonthlyTotal

 

Sort Order

MonthlyOpportunityTotal.OppMonth

 

Results Grid

MonthlyOpportunityTotal.OppMonth MIN Aggregation

MonthlyOpportunityTotal.MonthlyTotal MIN Aggregation

MonthlyOpportunityTotal2.MonthlyTotal SUM Aggregation

 

The results should look like the below:

 

View original
Did this topic help you find an answer to your question?

Forum|alt.badge.img+9
  • Semi-Pro I
  • October 18, 2023

Hi @john55 A generic inquiry should be prepared for the won opportunities. The generic Inquiry should be grouped by the column that has the formula Month(Date). This will provide the Won opportunities by Month. Using this GI, you can build the Dashboard widget of Bar/ line graph.

 


Forum|alt.badge.img
  • Freshman I
  • October 18, 2023

Thanks @ChandraM, that gets me the monthly totals, but how do I plot them on a chart cumulatively.  By cumulatively, I mean the Jan bar on the chart = Jan total, the Feb bar = Jan + Feb totals, the Mar bar = Jan + Feb + Mar totals, and so on.


Forum|alt.badge.img+9
  • Semi-Pro I
  • October 20, 2023

Hi @john55 Please find the below steps to create the Column chart.

Result of the Dashboard:

 

Step 1: Generic Inquiry. Please see attached generic Inquiry with the details of opportunities.

Output of the Generic Inquiry -  for 2023 year by Month:
 

 

Step 2 : Dashboard Configuration:

  1.  
  2. Navigate to the specific Dashboard view (in my example, I have added the dashbaord to the Customer View)
  3. Click on Design. This makes the dashboard on design view that allows you to Add new widgets(DashBoard element)
  1. Select the Chart for the bar chart  requirement

v.Confgure Chart as below. Note: You can change the values and verify with any of the options after reviewing this example of chart.

  1. Adding Custom filter - I added @yearStart and @yearEnd to show the current year data. You can adjust this filter to show the date range of the required data.
    1. Configure the graph(chart) - Click on Configure option in the above screen.
      Note: Please enter the values as shown below, atleast for the first review.
    2. Click OK in the above screen and click Finish on the main popup, as shown below. This will show you the preview of the dashbaord.

      ix. Adjust the size of the dashboard by dragging on the right bottom corner(if required)
      1. Exit from the design view by clicking on the “Design menu”, as shown below.
      2. Tip: you can manually refresh data to see the new data quickly by clicking on the below option. Note: This option will be shown when the mouse cursor is hovered on the graph.

         

Forum|alt.badge.img+9
  • Semi-Pro I
  • October 20, 2023

Hi @john55 Please review the above solution and kindly provide your feedback.

Thanks!


Forum|alt.badge.img
  • Freshman I
  • October 21, 2023

@ChandraM Thank you for you attention on this question, I really appreciate it!

The solution you describe still isn’t cumulative.  If it were cumulative, the 2023-Jan amount would be 52,250,000 (as it currently is), 2023-Feb would be 194,750,000 (52,250,000 + 142,500,000), 2023-Mar would be 337,250,000 (52,250,000 + 142,500,000 + 142,500,000), and so on.


Chris Hackett
Community Manager
Forum|alt.badge.img
  • Acumatica Community Manager
  • November 29, 2023

Hi @john55 were you able to find a solution? Thank you!


bheyns05
Jr Varsity III
Forum|alt.badge.img
  • Jr Varsity III
  • April 30, 2024

Hi John

 

I am also looking for cumulative graph & GI solutions. Have you found a solution on this?

 

Kind regards,

Bettie


ejmillar
Varsity I
Forum|alt.badge.img+1
  • Varsity I
  • May 1, 2024

Hi John,

 

I’ve found a solution to create a cumulative total.

However, Acumatica will need to be updated to 2024 R1.

The 2024 R1 update allows the ability to create a GI from another GI and I’ve used this technique to create a cumulative total for the opportunities.

The first step is to create a simple GI called ‘MonthlyOpportunityTotal’ that aggregates each month's opportunity amount.

Data sources:

CROpportunity

DateInfo

 

Relations

DateInfo.date Inner Join CROpportunity.closeDate

 

Conditions

CROpportunity.Status = ‘Won’

CROpportunity.CloseDateYear = 2014 (I’m using the sales demo system and 2014 has data)

 

Grouping

DateInfo.Month

 

Sort Order

DateInfo.Month

 

Result Grid

DateInfo.Month AS OppMonth

CROpportunity.Amount SUM Aggregation AS MonthlyTotal

 

The results should look like the below:

 

In the second part, a new GI needs to be created, which pulls in the GI above to calculate the cumulative total.

 

In Acumatica 2024 R1, we can now add a new GI as a datasource to a GI. 

Datasource

MonthlyOpportunityTotal Alias MonthlyOpportunityTotal

MonthlyOpportunityTotal Alias MonthlyOpportunityTotal2

 

Relations

MonthlyOpportunityTotal.DateInfo_Month Is Greater Than or Equal To MonthlyOpportunityTotal2.DateInfo_Month

 

Grouping

MonthlyOpportunityTotal.OppMonth

MonthlyOpportunityTotal.MonthlyTotal

 

Sort Order

MonthlyOpportunityTotal.OppMonth

 

Results Grid

MonthlyOpportunityTotal.OppMonth MIN Aggregation

MonthlyOpportunityTotal.MonthlyTotal MIN Aggregation

MonthlyOpportunityTotal2.MonthlyTotal SUM Aggregation

 

The results should look like the below:

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings