[Skip to main content](#vessel-content) # Sales breakdown by Category broken ## - **[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")** - [![mojooutdoors-holden's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Topic Author - [ Offline](/forum/user/12526-mojooutdoors-holden) Less More - Posts: 303 - Thank you received: 18 - - 8 years 3 weeks ago - 8 years 3 weeks ago [\#299359](#299359) by [mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") *[Sales breakdown by Category broken](/forum/36-reports-statistics/896003-sales-breakdown-by-category-broken#299359 "-- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: with the following settings (no category filter): Now if i choose the first master category (products in this case): with settings (only thing different is "Products" selected as the category): Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.")* was created by *[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")* -- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: This message contains confidential information with the following settings (no category filter): This message contains confidential information Now if i choose the first master category (products in this case): This message contains confidential information with settings (only thing different is "Products" selected as the category): This message contains confidential information Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much. - [ ![2018-10-1923_30_28-MOJOOutdoors-Administration-Report_2018-10-20-2.png](/forum/attachment/45149) ](/forum/attachment/45149 "...18-10-20-2.png") [ ](/forum/attachment/45149?download=1 "...18-10-20-2.png") - [ ![2018-10-1923_30_54-MOJOOutdoors-Administration-Report_2018-10-20-3.png](/forum/attachment/45152) ](/forum/attachment/45152 "...18-10-20-3.png") [ ](/forum/attachment/45152?download=1 "...18-10-20-3.png") - [ ![2018-10-1923_32_39-MOJOOutdoors-Administration-Report.png](/forum/attachment/45153) ](/forum/attachment/45153 "...ion-Report.png") [ ](/forum/attachment/45153?download=1 "...ion-Report.png") - [ ![2018-10-1923_32_56-MOJOOutdoors-Administration-Report.png](/forum/attachment/45154) ](/forum/attachment/45154 "...ion-Report.png") [ ](/forum/attachment/45154?download=1 "...ion-Report.png") Last edit: 8 years 3 weeks ago by [mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile"). Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy84OTYwMDMtc2FsZXMtYnJlYWtkb3duLWJ5LWNhdGVnb3J5LWJyb2tlbj90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Away](/forum/user/63-nicolas) Less More - Posts: 86555 - Thank you received: 14304 - - 8 years 3 weeks ago [\#299404](#299404) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Sales breakdown by Category broken](/forum/36-reports-statistics/896003-sales-breakdown-by-category-broken#299404 "-- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: with the following settings (no category filter): Now if i choose the first master category (products in this case): with settings (only thing different is "Products" selected as the category): Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.")* Hi, The fact that the products are in several categories is probably what's messing the amounts as the sales must be counted once for each category of the products in that case. Could you turn on the "debug mode" setting of the Joomla configuration and look at the report again ? Then, at the bottom of the page, could you search for the MySQL queries with the text hikashop_order_product in it and provide them ? Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy84OTYwMDMtc2FsZXMtYnJlYWtkb3duLWJ5LWNhdGVnb3J5LWJyb2tlbj90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")** - [![mojooutdoors-holden's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Topic Author - [ Offline](/forum/user/12526-mojooutdoors-holden) Less More - Posts: 303 - Thank you received: 18 - - 8 years 3 weeks ago [\#299441](#299441) by [mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Replied by *[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")* on topic *[Sales breakdown by Category broken](/forum/36-reports-statistics/896003-sales-breakdown-by-category-broken#299441 "-- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: with the following settings (no category filter): Now if i choose the first master category (products in this case): with settings (only thing different is "Products" selected as the category): Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.")* Here are the query results: [ ![2018-10-2215_19_51-Report.png](/forum/attachment/45164) ](/forum/attachment/45164 "..._51-Report.png") [ ![2018-10-2215_20_19-Report.png](/forum/attachment/45165) ](/forum/attachment/45165 "..._19-Report.png") Let me know if you need any further information. - [ ![2018-10-2215_19_51-Report.png](/forum/attachment/45164) ](/forum/attachment/45164 "..._51-Report.png") [ ](/forum/attachment/45164?download=1 "..._51-Report.png") - [ ![2018-10-2215_20_19-Report.png](/forum/attachment/45165) ](/forum/attachment/45165 "..._19-Report.png") [ ](/forum/attachment/45165?download=1 "..._19-Report.png") Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy84OTYwMDMtc2FsZXMtYnJlYWtkb3duLWJ5LWNhdGVnb3J5LWJyb2tlbj90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Away](/forum/user/63-nicolas) Less More - Posts: 86555 - Thank you received: 14304 - - 8 years 3 weeks ago [\#299477](#299477) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Sales breakdown by Category broken](/forum/36-reports-statistics/896003-sales-breakdown-by-category-broken#299477 "-- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: with the following settings (no category filter): Now if i choose the first master category (products in this case): with settings (only thing different is "Products" selected as the category): Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.")* Hi, Thank you for that. From what I can see, the issue is that the code: Code: if($widget->widget_params->content=='orders'){ $selectAdd='prod.order_product_quantity'; }else if($widget->widget_params->content=='sales'){ $selectAdd='(prod.order_product_price+prod.order_product_tax)*prod.order_product_quantity'; } should be added after the code: Code: if(!isset($leftjoin['order_product'])){ $leftjoin['order_product'] = ' LEFT JOIN '.hikashop_table('order_product').' AS prod ON prod.order_id = a.order_id '; } if(!isset($leftjoin['product_category'])){ $leftjoin['product_category'] = ' LEFT JOIN '.hikashop_table('product_category').' AS cat ON cat.product_id = prod.product_id '; } $leftjoin['category'] = ' LEFT JOIN '.hikashop_table('category').' AS c ON c.category_id = cat.category_id '; in the file administrator/components/com_hikashop/classes/widget.php That way, it will calculate the sales based on the amounts of the individual products in the order instead of the whole order amount. Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy84OTYwMDMtc2FsZXMtYnJlYWtkb3duLWJ5LWNhdGVnb3J5LWJyb2tlbj90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")** - [![mojooutdoors-holden's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Topic Author - [ Offline](/forum/user/12526-mojooutdoors-holden) Less More - Posts: 303 - Thank you received: 18 - - 8 years 3 weeks ago [\#299510](#299510) by [mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Replied by *[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")* on topic *[Sales breakdown by Category broken](/forum/36-reports-statistics/896003-sales-breakdown-by-category-broken#299510 "-- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: with the following settings (no category filter): Now if i choose the first master category (products in this case): with settings (only thing different is "Products" selected as the category): Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.")* I added the code provided and observed similar behavior. [ ![2018-10-2312_33_00-Report.png](/forum/attachment/45172) ](/forum/attachment/45172 "..._00-Report.png") [ ![2018-10-2312_32_13-Report.png](/forum/attachment/45173) ](/forum/attachment/45173 "..._13-Report.png") - [ ![2018-10-2312_33_00-Report.png](/forum/attachment/45172) ](/forum/attachment/45172 "..._00-Report.png") [ ](/forum/attachment/45172?download=1 "..._00-Report.png") - [ ![2018-10-2312_32_13-Report.png](/forum/attachment/45173) ](/forum/attachment/45173 "..._13-Report.png") [ ](/forum/attachment/45173?download=1 "..._13-Report.png") Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy84OTYwMDMtc2FsZXMtYnJlYWtkb3duLWJ5LWNhdGVnb3J5LWJyb2tlbj90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Away](/forum/user/63-nicolas) Less More - Posts: 86555 - Thank you received: 14304 - - 8 years 3 weeks ago [\#299536](#299536) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Sales breakdown by Category broken](/forum/36-reports-statistics/896003-sales-breakdown-by-category-broken#299536 "-- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: with the following settings (no category filter): Now if i choose the first master category (products in this case): with settings (only thing different is "Products" selected as the category): Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.")* Hi, Ok, then try to add the same code, but after the code: Code: $leftjoin['product'] = ' LEFT JOIN '.hikashop_table('product').' AS p ON prod.product_id = p.product_id '; $select .= 'DISTINCT(prod.order_product_id), '; in that same file. That should help better. Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy84OTYwMDMtc2FsZXMtYnJlYWtkb3duLWJ5LWNhdGVnb3J5LWJyb2tlbj90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")** - [![mojooutdoors-holden's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Topic Author - [ Offline](/forum/user/12526-mojooutdoors-holden) Less More - Posts: 303 - Thank you received: 18 - - 8 years 2 weeks ago [\#299581](#299581) by [mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Replied by *[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")* on topic *[Sales breakdown by Category broken](/forum/36-reports-statistics/896003-sales-breakdown-by-category-broken#299581 "-- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: with the following settings (no category filter): Now if i choose the first master category (products in this case): with settings (only thing different is "Products" selected as the category): Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.")* That addition broke the page. [ ![2018-10-2421_09_53-Window.png](/forum/attachment/45181) ](/forum/attachment/45181 "..._53-Window.png") Any other suggestions? - [ ![2018-10-2421_09_53-Window.png](/forum/attachment/45181) ](/forum/attachment/45181 "..._53-Window.png") [ ](/forum/attachment/45181?download=1 "..._53-Window.png") Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy84OTYwMDMtc2FsZXMtYnJlYWtkb3duLWJ5LWNhdGVnb3J5LWJyb2tlbj90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Away](/forum/user/63-nicolas) Less More - Posts: 86555 - Thank you received: 14304 - - 8 years 2 weeks ago [\#299586](#299586) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Sales breakdown by Category broken](/forum/36-reports-statistics/896003-sales-breakdown-by-category-broken#299586 "-- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: with the following settings (no category filter): Now if i choose the first master category (products in this case): with settings (only thing different is "Products" selected as the category): Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.")* Hi, This modification will not create that error. So I don't see why you would get that error. First, I would recommend to revert the change and see if your still have the error. Then, turn on the error reporting and debug mode settings of the Joomla configuration and try again. If you get that error again, you should get more information on the issue. Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy84OTYwMDMtc2FsZXMtYnJlYWtkb3duLWJ5LWNhdGVnb3J5LWJyb2tlbj90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")** - [![mojooutdoors-holden's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Topic Author - [ Offline](/forum/user/12526-mojooutdoors-holden) Less More - Posts: 303 - Thank you received: 18 - - 8 years 2 weeks ago [\#299664](#299664) by [mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Replied by *[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")* on topic *[Sales breakdown by Category broken](/forum/36-reports-statistics/896003-sales-breakdown-by-category-broken#299664 "-- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: with the following settings (no category filter): Now if i choose the first master category (products in this case): with settings (only thing different is "Products" selected as the category): Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.")* Here's more info on the error: [ ![2018-10-2523_21_04-Window.png](/forum/attachment/45197) ](/forum/attachment/45197 "..._04-Window.png") For now I have removed the code. I added it to the requested location in administrator/components/com_hikashop/classes/widget.php which was around line 646. Code: if($setFilters){ if(isset($widget->widget_params->filters['cat.category_id'])){ if(($widget->widget_params->content=='customers' || $widget->widget_params->content=='partners')){ $leftjoin['order'] = ' LEFT JOIN '.hikashop_table('order').' AS o ON o.order_user_id = a.user_id '; $leftjoin['order_product'] = ' LEFT JOIN '.hikashop_table('order_product').' AS prod ON prod.order_id = o.order_id '; }else{ $leftjoin['order_product'] = ' LEFT JOIN '.hikashop_table('order_product').' AS prod ON prod.order_id = a.order_id '; } $leftjoin['product'] = ' LEFT JOIN '.hikashop_table('product').' AS p ON prod.product_id = p.product_id '; $select .= 'DISTINCT(prod.order_product_id), '; if($widget->widget_params->content=='orders'){ $selectAdd='prod.order_product_quantity'; }else if($widget->widget_params->content=='sales'){ $selectAdd='(prod.order_product_price+prod.order_product_tax)*prod.order_product_quantity'; } $leftjoin['product_category'] = ' LEFT JOIN '.hikashop_table('product_category').' AS cat ON cat.product_id = p.product_id OR cat.product_id=p.product_parent_id'; if($widget->widget_params->category_childs){ $leftjoin['category'] = ' LEFT JOIN '.hikashop_table('category').' AS categ ON cat.category_id = categ.category_id '; $widget->widget_params->filters['cat.category_id']=array_merge($widget->widget_params->filters['cat.category_id'], $widget->widget_params->childs); } } if(isset($widget->widget_params->filters['prod.product_id'])){ if(($widget->widget_params->content=='customers' || $widget->widget_params->content=='partners')){ $leftjoin['order'] = ' LEFT JOIN '.hikashop_table('order').' AS o ON o.order_user_id = a.user_id '; $leftjoin['order_product'] = ' LEFT JOIN '.hikashop_table('order_product').' AS prod ON prod.order_id = o.order_id '; }else{ $leftjoin['order_product'] = ' LEFT JOIN '.hikashop_table('order_product').' AS prod ON prod.order_id = a.order_id '; } } } - [ ![2018-10-2523_21_04-Window.png](/forum/attachment/45197) ](/forum/attachment/45197 "..._04-Window.png") [ ](/forum/attachment/45197?download=1 "..._04-Window.png") Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy84OTYwMDMtc2FsZXMtYnJlYWtkb3duLWJ5LWNhdGVnb3J5LWJyb2tlbj90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Away](/forum/user/63-nicolas) Less More - Posts: 86555 - Thank you received: 14304 - - 8 years 2 weeks ago [\#299700](#299700) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Sales breakdown by Category broken](/forum/36-reports-statistics/896003-sales-breakdown-by-category-broken#299700 "-- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: with the following settings (no category filter): Now if i choose the first master category (products in this case): with settings (only thing different is "Products" selected as the category): Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.")* Hi, So you need to keep that change but also change the line: $pie= 'SUM('.$selectAdd.') AS total'; to: $pie= 'SUM('.$selectAdd.') AS total, a.order_currency_id AS currency_id'; in that same file. That will avoid that query error which happens because the way the calculations are done changed with the modification I proposed. Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy84OTYwMDMtc2FsZXMtYnJlYWtkb3duLWJ5LWNhdGVnb3J5LWJyb2tlbj90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")** - [![mojooutdoors-holden's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Topic Author - [ Offline](/forum/user/12526-mojooutdoors-holden) Less More - Posts: 303 - Thank you received: 18 - - 8 years 5 days ago [\#300350](#300350) by [mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Replied by *[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")* on topic *[Sales breakdown by Category broken](/forum/36-reports-statistics/896003-sales-breakdown-by-category-broken#300350 "-- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: with the following settings (no category filter): Now if i choose the first master category (products in this case): with settings (only thing different is "Products" selected as the category): Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.")* No error now, but the report is still incorrect. Seems to be double counting since they are in multiple categories. The amount of products in both master categories is very small and would not explain the discrepancy. I should also note that some products inside "Products" and "replacement parts" are also in multiple subcategories. For example, Part 1a might be in 4 subcategories of "replacement parts." Does this make sense? Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy84OTYwMDMtc2FsZXMtYnJlYWtkb3duLWJ5LWNhdGVnb3J5LWJyb2tlbj90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Away](/forum/user/63-nicolas) Less More - Posts: 86555 - Thank you received: 14304 - - 8 years 2 days ago - 8 years 2 days ago [\#300408](#300408) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Sales breakdown by Category broken](/forum/36-reports-statistics/896003-sales-breakdown-by-category-broken#300408 "-- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: with the following settings (no category filter): Now if i choose the first master category (products in this case): with settings (only thing different is "Products" selected as the category): Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.")* Hi, Yes, the fix until now what to take the sum of the product amount for all matched orders instead of using the total amount of the order. Now, it doesn't help if the product is in several categories of the categories selected in the "category" setting of the report as it will sum it once per category matching in that setting. So if you select there the main category with all it's sub categories, it will sum the amount for each sub categories. Since you're selected there the main category, I would recommend instead to not select it and leave the field empty. That way, the problem won't happen. Alternatively, you could select only the categories so that no doublon happens. *This attachment is hidden for guests. Please log in or register to see it.* I've added a fix on our end to avoid that. However, it's a big patch so I can't just give you the lines to change as there are 30 lines at different places. Here is a zip of the widget.php with the patch: So you can unzip it and replace the file. Note however that you're using HikaShop 3.5.0 and this file is the one from HikaShop 4.0.0. It should be ok but it might create some unforseen issues. Last edit: 8 years 2 days ago by [nicolas](/forum/user/63-nicolas "View nicolas's Profile"). Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy84OTYwMDMtc2FsZXMtYnJlYWtkb3duLWJ5LWNhdGVnb3J5LWJyb2tlbj90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")** - [![mojooutdoors-holden's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Topic Author - [ Offline](/forum/user/12526-mojooutdoors-holden) Less More - Posts: 303 - Thank you received: 18 - - 8 years 1 day ago [\#300482](#300482) by [mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Replied by *[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")* on topic *[Sales breakdown by Category broken](/forum/36-reports-statistics/896003-sales-breakdown-by-category-broken#300482 "-- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: with the following settings (no category filter): Now if i choose the first master category (products in this case): with settings (only thing different is "Products" selected as the category): Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.")* Broke the report, but it's probably because of the change from 3.5.0 to 4.0.0. I will have to wait until after our busy season to upgrade. In the meantime, I think I might just manually select all of the products. Surely that will prevent double counting? Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy84OTYwMDMtc2FsZXMtYnJlYWtkb3duLWJ5LWNhdGVnb3J5LWJyb2tlbj90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Away](/forum/user/63-nicolas) Less More - Posts: 86555 - Thank you received: 14304 - - 8 years 17 hours ago [\#300487](#300487) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Sales breakdown by Category broken](/forum/36-reports-statistics/896003-sales-breakdown-by-category-broken#300487 "-- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: with the following settings (no category filter): Now if i choose the first master category (products in this case): with settings (only thing different is "Products" selected as the category): Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.")* Hi, If you don't set a category, it will work properly, yes. That's what I said in my previous message. The following user(s) said Thank You: [mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy84OTYwMDMtc2FsZXMtYnJlYWtkb3duLWJ5LWNhdGVnb3J5LWJyb2tlbj90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")** - [![mojooutdoors-holden's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Topic Author - [ Offline](/forum/user/12526-mojooutdoors-holden) Less More - Posts: 303 - Thank you received: 18 - - 7 years 11 months ago [\#301150](#301150) by [mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile") Replied by *[mojooutdoors-holden](/forum/user/12526-mojooutdoors-holden "View mojooutdoors-holden's Profile")* on topic *[Sales breakdown by Category broken](/forum/36-reports-statistics/896003-sales-breakdown-by-category-broken#301150 "-- HikaShop version -- : 3.5.0 -- Joomla version -- : 3.8.12 -- PHP version -- : 7.1 Situation: I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month: with the following settings (no category filter): Now if i choose the first master category (products in this case): with settings (only thing different is "Products" selected as the category): Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect. I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.")* Gotcha. I misunderstood. It took some time to manually select each individual product, but the report now displays properly. Thanks for your help! Hopefully, I will be able to upgrade our hikashop version after the first of the year. Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy84OTYwMDMtc2FsZXMtYnJlYWtkb3duLWJ5LWNhdGVnb3J5LWJyb2tlbj90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. Board Categories HikaShop Install & Update Product & Category Display Content Creation Sales and Orders Orders management Reports & Statistics Customers Checkout Payment Methods Shipping Methods Import & Export (CSV, XLS…) Specific Features Affilation Taxes Wishlist Vote Carousel & Slider Mass Actions Filters & Search Discount & Coupon Feature Request Integrations Showcase General Talk about HikaShop Development HikaShop 3.0 Beta Translation Commercial Jobs HikaShop Components HikaMarket Market: Feature requests Market: How to? Market: Bug Report HikaSerial General talk about HikaSerial Serial: Bug Report Serial: How to? Support en Français de HikaSerial (French Support) HikaSubscription HikaAuction Auction: Feature requests Auction: How to? Auction: Bug report Hikashop around the world Support en Français (French Support) Russian FAQ. Курс выживания с Hikashop. Частые вопросы и о Расширения Hikashop Наши сайты на Hikashop 日本語版サポート(Japanese Support) Thai German Archives Bug Report How to? [](/forum/topics/feed/mode-topics?format=feed&type=rss) Time to create page: 0.104 seconds [Powered by](/forum/credits) [Kunena Forum](https://www.kunena.org)