search

ivylabs / suitecrm-analytics

4 stars 4 forks source link

Periodic Agent Country Sales - "Total Cost" calculation appears to be inaccurate to real Totals #297

Closed johnM2401 closed 3 years ago

johnM2401 commented 3 years ago

Describe the bug When the "Total Cost" is calculated on the Periodic Agent Country Sales report, it appears to be calculating the Cumulative Total Cost of Unique Products on Line Items * Quantity of Line Items

Rather than being an accurate Total of: Cost of Each Product * Quantity Set on the Line Items

So, I don't believe this column gives an entirely accurate Total Cost


For example;

If you were to have two Invoices assigned to an Agent;

The Cumulative Cost of the Products used in these Line Items is $10

(1 Item with a cost of 5, 5 items with a Cost of 1)


However, the "Total Cost" value appears to come out as $36, on the Periodic Agent Country Sales ($5+$1)*(6)

Please correct me if I'm wrong, but I think the most accurate calculation in this scenario would instead be: ($5 Cost 1 Quantity) + ($1 Cost 5 Quantity)

Unless I've misunderstood the intention?


I believe that this could be a compounding issue, if the user has various Quantities of Line Items with varying costs. This could end up resulting in "Total Cost" values that don't reflect the real data.

See Screenshots for an Example


Component What component does this bug relate to?

  1. Report - Periodic Agent Country Sales report

Solution File PeriodicAgentCountrySales.prpt

To Reproduce Steps to reproduce the behavior:

  1. Create two Products, One with a cost of $5. Another with a cost of $1.
  2. Create an Invoice, add a Single Line item, Choosing the $5-Cost Product, with a Quantity of 1
  3. Create a second Invoice, add a Single Line item, Choosing the $1-Cost Product, with a Quantity of 5
  4. Pull this to the DWH with the ./run script
  5. View the Agent Sales Report, to see how this is shown, broken down by Line Item ($10 Total Cost)
  6. View the Periodic Agent Country Sales Report, to see how this is shown. ($36 Total Cost)

Expected behavior The "Total Cost" will show an accurate running number. (ie, Total Cost of Each Product * Line Item Quantity)


Screenshots

A screenshot of the "Agent Sales Report": This shows two line items, with a Total Cost of $10 for this Country image

However, the same Country shows a Total cost of $36 on the Periodic Agent Country Sales report image

Desktop (please complete the following information):



Please let me know if anything needs clarified!

fraserward commented 3 years ago

Thanks @johnM2401 . That one got flew under the radar.

A SQL error in the PeriodicAgentCountrySales report.

Should all be fixed now.

Can you retest the branch -->> https://github.com/ivylabs/suitecrm-analytics/tree/feature-297

johnM2401 commented 3 years ago

Hey @fraserward

Looks good to me. However, I have noticed a similar issue with the Agent Sales Report, when the same Products are spread over multiple Invoices. Will raise this in just a moment.