Product wise Sales Analytics 2 year comparison custom query report

with sales difference between 2 years | Total Amt. | Diff. Amt.

SELECT `tabSales Invoice Item`.`item_code` as `Item:Link/Item:150`,

SUM(IF(`tabSales Invoice`.`posting_date` between "2019-04-01" and "2019-04-30", amount, 0)) AS `Apr 19-20::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2019-05-01" and "2019-05-31", amount, 0)) AS `May 19-20::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2019-06-01" and "2019-06-30", amount, 0)) AS `June 19-20::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2019-07-01" and "2019-07-31", amount, 0)) AS `July 19-20::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2019-08-01" and "2019-08-31", amount, 0)) AS `Aug 19-20::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2019-09-01" and "2019-09-30", amount, 0)) AS `Sep 19-20::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2019-10-01" and "2019-10-31", amount, 0)) AS `Octo 19-20::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2019-11-01" and "2019-11-30", amount, 0)) AS `Nov 19-20::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2019-12-01" and "2019-12-31", amount, 0)) AS `Dec 19-20::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-01-01" and "2020-01-31", amount, 0)) AS `Jan 19-20::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-02-01" and "2020-02-29", amount, 0)) AS `Feb 19-20::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-03-01" and "2020-03-31", amount, 0)) AS `Mar 19-20::100`,

SUM(IF(`tabSales Invoice`.`posting_date` between "2019-04-01" and "2020-03-31", amount, 0)) AS  `TOTAL 19-20::100`,


SUM(IF(`tabSales Invoice`.`posting_date` between "2020-04-01" and "2020-04-30", amount, 0)) AS `Apr 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-05-01" and "2020-05-31", amount, 0)) AS `May 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-06-01" and "2020-06-30", amount, 0)) AS `June 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-07-01" and "2020-07-31", amount, 0)) AS `July 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-08-01" and "2020-08-31", amount, 0)) AS `Aug 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-09-01" and "2020-09-30", amount, 0)) AS `Sep 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-10-01" and "2020-10-31", amount, 0)) AS `Octo 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-11-01" and "2020-11-30", amount, 0)) AS `Nov 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2020-12-01" and "2020-12-31", amount, 0)) AS `Dec 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2021-01-01" and "2021-01-31", amount, 0)) AS `Jan 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2021-02-01" and "2021-02-28", amount, 0)) AS `Feb 20-21::100`,
SUM(IF(`tabSales Invoice`.`posting_date` between "2021-03-01" and "2021-03-31", amount, 0)) AS `Mar 20-21::100`,

SUM(IF(`tabSales Invoice`.`posting_date` between "2020-04-01" and "2021-03-31", amount, 0)) AS `TOTAL 20-21::100`,

SUM(IF(`tabSales Invoice`.`posting_date` between "2020-04-01" and "2021-03-31", amount, 0) - IF(`tabSales Invoice`.`posting_date` between "2019-04-01" and "2020-03-31", amount, 0)) AS `Diff. Amt.::100`




    FROM `tabSales Invoice Item`,`tabSales Invoice`
    where `tabSales Invoice Item`.`parent` = `tabSales Invoice`.`name` 
    and `tabSales Invoice`.docstatus = 1 


Group by `item_code`

Thanks.

2 Likes

It’s work
thanks

thanks
it’s very helpful for me