News:

You may pay someone to create your store, or you visit our seminar and become a professional yourself with the silver certification

Main Menu

Incorrect BE display of revenue reports by month and week

Started by man.of.earth, December 11, 2019, 15:36:36 PM

Previous topic - Next topic

man.of.earth

Hello developers,

I noticed that in BE, in the revenue reports, if I choose ,,Interval: monthly" and a certain period for more than 1 year, the figures corresponding to each month are calculated for the same month of several years (I would expect it to calculate it for each month from the selected interval).
For instance, if the period is 01.01.2016 until 31.12.2017 (2 years), there are shown only 12 months, the figures for each month being the sum of the figures from the same month from both years (i.e. March = March 2016 + March 2017).
The same happens for weeks. Only 53 weeks are shown, even if it's about multiple years, each one being the sum of the same weeks number from several years selected.
The query for months is
SELECT MONTH( o.created_on ) AS intervals, o.`created_on` AS created_on, SUM( (product_discountedPriceWithoutTax * product_quantity)) as order_subtotal_netto, SUM(product_subtotal_with_tax) as order_subtotal_brutto, SUM(coupon_discount) as coupon_discount, COUNT(DISTINCT o.virtuemart_order_id) as count_order_id, SUM(product_quantity) as product_quantity FROM `#__virtuemart_order_items` as i
LEFT JOIN `#__virtuemart_orders` as o ON o.virtuemart_order_id=i.virtuemart_order_id
WHERE (`i`.`order_status` = "S" OR `i`.`order_status` = "F") AND o.created_on BETWEEN "2016-01-01 02:00:00" AND "2018-01-01 01:59:59"
GROUP BY intervals
ORDER BY created_on DESC

and the onw for weeks is
SELECT WEEK( o.created_on ) AS intervals, o.`created_on` AS created_on, SUM( (product_discountedPriceWithoutTax * product_quantity)) as order_subtotal_netto, SUM(product_subtotal_with_tax) as order_subtotal_brutto, SUM(coupon_discount) as coupon_discount, COUNT(DISTINCT o.virtuemart_order_id) as count_order_id, SUM(product_quantity) as product_quantity FROM `#__virtuemart_order_items` as i
LEFT JOIN `#__virtuemart_orders` as o ON o.virtuemart_order_id=i.virtuemart_order_id
WHERE (`i`.`order_status` = "S" OR `i`.`order_status` = "F") AND o.created_on BETWEEN "2016-01-01 02:00:00" AND "2018-01-01 01:59:59"
GROUP BY intervals
ORDER BY created_on DESC



Jörgen

Yes, that is how it has worked for over 10 years, You get summed revenue per month over several years.
I do not know if this is intended, but you will get the revenue for each month if You set 1/1 to 31/12 for one year at a time.

Jörgen @ Kreativ Fotografi
Joomla 3.9.18
Virtuemart 3.4.x
Olympiantheme Hera (customized)
This reflects current status when viewing old post.