Script Report Field Compared to Field on Different Date

Hello!

I’m making some headway on creating the reports we need in ERPNext but I’m really struggling to wrap my head around one particular field.

Each store creates a new document each day, and the report shows all the store’s data based on a date filter. I need to create a column that takes a field value from each store’s document for the selected date and subtracts the same field from the document from the day before, showing the difference.

I’ve tried working it into the report.py as well as creating a new field on the DocType with a script to get the day priors and I just can’t seem to get it to work.

Any suggestions on the best way to go about this would be great! Thank you!!

For sake of example, let’s say the documents you’re working with are Sales Invoices.

You could get the results with ordinary Python. For each row of your report, fetch today’s value. Then yesterday’s value. Then subtract. That’s a lot of looping, though.

Another approach would be creating 2 datasets (one for each day), then calculating the difference between columns, using something like pandas.

But were this me? I’d let SQL do all the work. It’s ideal for doing set-based calculations like this, and much faster than looping over rows:

SET @DateToday = '2022-05-23';

SELECT
	 InvoiceToday.name
	,InvoiceToday.customer
	,InvoiceToday.grand_total					AS grand_total_today
	,IFNULL(InvoiceYesterday.grand_total, 0.00)	AS grand_total_yesterday
	,InvoiceToday.grand_total - IFNULL(InvoiceYesterday.grand_total, 0.00)	AS difference

FROM
	`tabSales Invoice`	AS InvoiceToday

LEFT JOIN	
	`tabSales Invoice`	AS InvoiceYesterday
ON
	InvoiceYesterday.customer = InvoiceToday.customer
AND InvoiceYesterday.posting_date = DATE_ADD(InvoiceToday.posting_date, INTERVAL -1 DAY)
AND InvoiceYesterday.company = InvoiceToday.company

WHERE
	InvoiceToday.posting_date = @DateToday;  # substitute %(posting_date)s if calling with Python.

Results would look something like this:

In your report.py, call your SQL query using the function frappe.db.sql().

The key to success is getting the SQL precisely right, in terms of cardinality. So the joins are 1:1. For example, my query will completely fail, if there is more than 1 invoice for the same customer, on the same date. (This is solvable by writing sub-queries, but I didn’t want to make this example too complex).

Thank you so much! This sent me in exactly the right direction!

1 Like