CHART SUM / TOTAL / GROUP BY

lineamovil

Hello,

I have been working with charts recenlty with AwareIM, and then changed for a time to FusionCharts.

To be honest I really wanted to use only AwareIm and then took a dive into the charts and went great with easy (not to complex) charts.

Here is a sample:

But now I need to use a more complex Graph / Chart.

Here is the thing:

BO called INVOICE
I need to make a chart with these fields:
CLIENT
PURCHASE_DATE
SALES_TOTAL

There are many sales in the BO INVOICE and I want to show a Chart showing the TOTAL AMOUNT sold to EACH CLIENT in a period, or current month.

I dont know how to "GROUP BY"

I create an empty BO to store the results of my previous charts, but have no idea in how to "Group By" and SUM the total amount "$" per client.

thanks for your help!

Sincerely,

Carlos

aware_support

You will most probably need to create a special object to represent records shown on the chart. This object will have rules to perform calculations. The chart will the be straightforward. These objects can be recalculated regularly - for example, every night by scheduling rules.

Take a look how it is done in the CRM sample application.

lineamovil

Thank you!

I have done that you are telling me like this:

1.- Create a BO to gather the informarmation
Month, Total Amount

2.- Also in the same BO, I go to the Actions and then SUM each "Total Amount" by month

3.- Then, I create a PROCESS that deletes all the current records in the BO, then I Make the Rule. SUM,something like this:

CREATE graficaFacturas FOR EACH MONTH BETWEEN Date(1,1,CURRENT_YEAR) AND CURRENT_DATE WITH graficaFacturas.Fecha=LOOP_ITERATION, graficaFacturas.Mes=MONTH(LOOP_ITERATION)

4.- I go to the Querys and make the Chart.

To run all this, I have to Run first the PROCESS, and the open the cart.
I studied this using the CRM Template.

It is working with very simple charts like:
Month 1 = $1000
Month 2 = $2000
Month 3 = $5000
Month 4 = $2000
Month 5 = $1400

But Im strugling to make a more complex chart like this:

Client A - Month 1 = $1000
Client A - Month 2 = $2000
Client A - Month 3 = $5000
Client A - Month 4 = $2000
Client A - Month 5 = $1400

Client B - Month 1 = $1020
Client B - Month 2 = $2200
Client B - Month 3 = $5400
Client B - Month 4 = $2500
Client B - Month 5 = $1600

Client C - Month 1 = $1500
Client C - Month 2 = $5000
Client C - Month 3 = $5600
Client C - Month 4 = $2400
Client C - Month 5 = $1700

Thank you for any help!

RLJB

Hi Carlos

I think you are over complicating it (which is easily done sometimes 🙂 )

Try this:

Create a BO - call it "Stats"
In Stats BO create some attributes, like:

Type
Client
Year
MonthNumber
MonthText
Sales

In the Stats BO add some rules do your sums as you need to; eg

If Stats.Type='Sales' Then
Stats.Sales=SUM Invoices.Amount WHERE Invoices.Client=Stats.Client

Do a process to run your query/chart, but as the first part of the process UPDATE the relevant Stats BO:

FIND Stats WHERE Stats.Type='Sales'
UPDATE Sales
RunQueryWithChart

You will obviously need to make the Stats instances before you run the chart for the first time, and build a method into your app to keep the stats instances updated for new clients etc.

If you do it this way you can make the rules on the Stats BO do as complex a calculation as you require.

(FYI - at the end of the year run a process to update the Stats.Year attribute OR just add in another year so you can compare against last year)

Good luck.