How to make a monthly comparison of two queryset

Using Django Using the ORM
1

I have two querysets and I want to subtract the values ​​to obtain a monthly or e.g. weekly summary.

def statistics_three_months():
    now = timezone.now()
    three_months_ago = now - timedelta(days=3 * 30)

    vip_records = KasaRejestr.objects.filter(
        date__gte=three_months_ago,
        subscription_ticket="vip"
    ).annotate(
        month=TruncMonth('date'),
        source=Value('vip_records', output_field=models.CharField())
    ).values('month', 'source').annotate(
        result=Count('id')
    ).order_by('month', 'source')

    print(f"vip: {vip_records}")

    entries_tickets_vip_records = Ticket.objects.filter(
        EAN__regex="^[0-9]{16}$",
        created__gte=three_months_ago,
        car_in_sector__lt=3
    ).annotate(
        month=TruncMonth('created'),
        source=Value('entries_tickets_vip_records', output_field=models.CharField())
    ).values('month', 'source').annotate(
        count=Count('id')
    ).order_by('month', 'source')

    print(f"entries + vip: {entries_tickets_vip_records}")

    # Combine both sets of results manually
    all_data = list(vip_records) + list(entries_tickets_vip_records)

    # Group the combined results by month and source using a dictionary
    summary_dict = {}
    for item in all_data:
        key = (item['month'], item['source'])
        summary_dict[key] = summary_dict.get(key, 0) + item.get('result', 0) - item.get('count', 0)

    # Convert the grouped dictionary into a list of dictionaries
    summary = [{'month': key[0], 'total_diff': value, 'source': key[1]} for key, value in summary_dict.items()]

    print("---------------------")
    print(vip_records)
    print(entries_tickets_vip_records)
    print(summary)
    return summary

vip_records has January and November (no vip_records in December)
entries_tickets_vip_records has January, December and November.
As a result, I get 5 elements instead of 3 with incorrect results.
Example output

vip_records: <QuerySet [{'month': datetime.datetime(2023, 11, 1, 0, 0), 'source': 'vip_records', 'result': 2}, {'month': datetime.datetime(2024, 1, 1, 0, 0), 'source': 'vip_records', 'result': 4}]>
entries_tickets_vip_records: <QuerySet [{'month': datetime.datetime(2023, 11, 1, 0, 0), 'source': 'entries_tickets_vip_records', 'count': 2}, {'month': datetime.datetime(2023, 12, 1, 0, 0), 'source': 'entries_tickets_vip_records', 'count': 2}, {'month': datetime.datetime(2024, 1, 1, 0, 0), 'source': 'entries_tickets_vip_records', 'count': 8}]>
summary [{'month': datetime.datetime(2023, 11, 1, 0, 0), 'total_diff': 2, 'source': 'vip_records'}, {'month': datetime.datetime(2024, 1, 1, 0, 0), 'total_diff': 4, 'source': 'vip_records'}, {'month': datetime.datetime(2023, 11, 1, 0, 0), 'total_diff': -2, 'source': 'entries_tickets_vip_records'}, {'month': datetime.datetime(2023, 12, 1, 0, 0), 'total_diff': -2, 'source': 'entries_tickets_vip_records'}, {'month': datetime.datetime(2024, 1, 1, 0, 0), 'total_diff': -8, 'source': 'entries_tickets_vip_records'}]

I ran out of ideas and chatgpt didn’t come up with anything either :slight_smile:

2

I’m sorry, I’m not following what you’re trying to do here.

What is it that you’re trying to calculate?

Are you saying that statistics_three_months is not calculating the correct results?

If so, what would the correct results be?

(If it is calculating the correct results, then I’m not sure I understand what the issue is.)

3

Generally, this method is intended to extract all records with VIP status from the past 90 days from the Cash Register, divide them into months and count them. And the same with Ticket data. And then calculate the difference for the consecutive months.
Certainly one of the problems is when there are no records in a given month, then 0 should be substituted.