Profit and Loss statement daily

Maybe this is a simple workaround based on a query report:

SELECT 
  `tDates`.`Date`,
  /* aggregate income based on account type; alternatively, use `root_type` = 'Income' */
  (SELECT IFNULL(SUM(`tGL1`.`credit`) - SUM(`tGL1`.`debit`), 0) 
	 FROM `tabGL Entry` AS `tGL1`
	 JOIN `tabAccount` AS `tA1` ON `tGL1`.`account` = `tA1`.`name`
	 WHERE 
	   `tGL1`.`docstatus` = 1
	   AND `tA1`.`account_type` = 'Income Account'
	   AND `tGL1`.`posting_date` = `tDates`.`Date`) AS `Income`,
   /* aggregate expenses based on account type; alternatively, use `root_type` = 'Expense' */
  (SELECT IFNULL(SUM(`tGL2`.`credit`) - SUM(`tGL2`.`debit`), 0) 
	 FROM `tabGL Entry` AS `tGL2`
	 JOIN `tabAccount` AS `tA2` ON `tGL2`.`account` = `tA2`.`name`
	 WHERE 
	   `tGL2`.`docstatus` = 1
	   AND `tA2`.`account_type` = 'Expense Account'
	   AND `tGL2`.`posting_date` = `tDates`.`Date`) AS `Expense`     
FROM 
/* find distinct dates */
(SELECT 
  DISTINCT(`posting_date`) AS `Date`
FROM `tabGL Entry` AS `tGL`) AS `tDates`;

It will give you the dates with transactions and then income/expenses. I prefer the option with the root_type (it will equal the P&L), but you can also filter on account type…

Hope this helps.

1 Like