Home > Net >  what is the correct way to filter data from db in monthly and yearly basis for analytics?
what is the correct way to filter data from db in monthly and yearly basis for analytics?

Time:12-23

I am making an analytics section for my client, where I need to display data in graphical form. For this I am using chart js. Now I have a model to store visitors of the website. Now I want to filter the data in monthly and yearly basis to put it in chart js and display it graphically. Now what I am doing is this

monthly_visitor = VisitorCount.objects.all().filter(date_of_record__month = today.month).count()

yearly_visitor = VisitorCount.objects.all().filter(date_of_record__year = today.year).count()

Here, I am able to get the count for my current month and year. But making variables for each month and year is not the right to do it.

So please suggest me what is the correct procedure to filter the data so that I can make the analytics. I have never before done this, so I do no the correct approach, please suggest me.

Also the chart js code looks like this, which also perhaps no the correct way to do make the analytics

const vismonth = document.getElementById('visitorMonthly').getContext('2d');
const visitorMonthly = new Chart(vismonth, {
    type: 'bar',
    data: {
        labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
        datasets: [{
            label: 'Visitors Monthly',
            data: [12, 19, 3, 5, 2, 3],
            backgroundColor: [
                'rgba(255, 99, 132, 0.2)',
                'rgba(54, 162, 235, 0.2)',
                'rgba(255, 206, 86, 0.2)',
                'rgba(75, 192, 192, 0.2)',
                'rgba(153, 102, 255, 0.2)',
                'rgba(255, 159, 64, 0.2)'
            ],
            borderColor: [
                'rgba(255, 99, 132, 1)',
                'rgba(54, 162, 235, 1)',
                'rgba(255, 206, 86, 1)',
                'rgba(75, 192, 192, 1)',
                'rgba(153, 102, 255, 1)',
                'rgba(255, 159, 64, 1)'
            ],
            borderWidth: 1
        }]
    },
    options: {
        scales: {
            y: {
                beginAtZero: true
            }
        }
    }
});

const visyear = document.getElementById('visitorYearly').getContext('2d');
const visitorYearly = new Chart(visyear, {
    type: 'bar',
    data: {
        labels: ['2022', '2023', '2024', '2025', '2026', '2027'],
        datasets: [{
            label: 'Visitors Yearly',
            data: [12, 19, 3, 5, 2, 3],
            backgroundColor: [
                'rgba(255, 99, 132, 0.2)',
                'rgba(54, 162, 235, 0.2)',
                'rgba(255, 206, 86, 0.2)',
                'rgba(75, 192, 192, 0.2)',
                'rgba(153, 102, 255, 0.2)',
                'rgba(255, 159, 64, 0.2)'
            ],
            borderColor: [
                'rgba(255, 99, 132, 1)',
                'rgba(54, 162, 235, 1)',
                'rgba(255, 206, 86, 1)',
                'rgba(75, 192, 192, 1)',
                'rgba(153, 102, 255, 1)',
                'rgba(255, 159, 64, 1)'
            ],
            borderWidth: 1
        }]
    },
    options: {
        scales: {
            y: {
                beginAtZero: true
            }
        }
    }
});

Please suggest me what to do

CodePudding user response:

you can use annotate in django.

from django.db.models import Count

monthly_visitor = VisitorCount.objects.all().values(
    'date_of_record__month'
    ).annotate(
        total_in_month=Count('id') # take visitor_id or whatever you want to count
        ).order_by()

it will give you queryset something like.

<QuerySet [{'created_at__month': 1, 'total_in_month': 5458}, {'created_at__month': 2, 'total_in_month': 4555}]
  • Related