I have a django model that is "DailyReport" of the companies sale I want to find out company sale change over the previous day. the model that i define is like that:
class DailyReport(models.Model):
company = models.CharField(max_length=50)
sale = models.IntegerField()
date = models.DateField()
How can i figure out this issue to add new column for every report that represent change rate over the previous day
Use the Lag window function to annotate each row with the previous sale amount for that company.
Then use another annotation to calculate the difference between the current and previous sale
from django.db.models import Window, F
from django.db.models.functions import Lag
DailyReport.objects.annotate(
prev_val=Window(
expression=Lag('sale', default=0),
partition_by=['company'],
order_by=F('date').asc(),
)
).annotate(
diff=F('sale') - F('prev_val')
)
Assuming you can have only one record of a company for each day, you can create a model property:
@property
def previous_day_sale(self):
date = self.date
dr = DailyReport.objects.filter(company=self.company, date=date-timedelta(days=1)
if dr:
return dr.first().sale - self.sale
You may need to override the save method, but you will have to cover all edge cases.
class DailyReport(models.Model):
company = models.CharField(max_length=50)
sale = models.IntegerField()
date = models.DateField()
sale_over_previous_day = models.IntegerField()
def save(self, *args, **kwargs):
previous_day_sale_object = DailyReport.objects.filter(company=self.company, date=date-timedelta(days=1))
if previous_day_sale_object:
previous_day_sale = previous_day_sale_object[0].sale
else:
previous_day_sale = 0
self.sale_over_previous_day = self.sale - previous_day_sale
super(DailyReport, self).save(*args, **kwargs)
