Calculate statistics for number of clicks per week

Question

so i have a small application, that registers how many clicks are on certain statements.

I have a table called ClickStatistics Which stores the ID for the specific statement clicked, and a Datetime "LogDate".

I want to create a graph that tells me how many clicks there has been each week.

I have made something similar with months, but i am unable to figure out a way to do it for weeks.

var months = new[]{
                "Januar",
                "Februar",
                "Marts",
                "April",
                "Maj",
                "Juni",
                "Juli",
                "August",
                "September",
                "Oktober",
                "November",
                "December"
            };
var MonthlyCountQuery = from c in ClickStatistics
                                    where c.LogDate.Year == selectedYear && c.Statement.Deleted == false
                                    group c by c.LogDate.Month into grp
                                    select new
                                    {
                                        Month = grp.Key,
                                        Clicks = grp.Count(),
                                    };

            var monthlyCount = MonthlyCountQuery.ToList().Select(i => new { Month = months[i.Month - 1], i.Clicks});

Any tips would be welcome!

Which week standard do you want to use? https://en.wikipedia.org/wiki/ISO_week_date — Stefan, Aug 08 '18 at 10:50
... having said that: change your grouping to this answer, make sure you don't run this agains the sql server: https://stackoverflow.com/questions/11154673/get-the-correct-week-number-of-a-given-date#11155102 — Stefan, Aug 08 '18 at 10:52
Or this perhaps: https://msdn.microsoft.com/en-us/library/system.globalization.calendar.getweekofyear.aspx — Stefan, Aug 08 '18 at 10:54
Possible duplicate of [Get the correct week number of a given date](https://stackoverflow.com/questions/11154673/get-the-correct-week-number-of-a-given-date) — nilsK, Aug 08 '18 at 10:59
@nilsK: I somewhat argue the duplicate: I think this question is more about the actual grouping and using a "custom function" to do that. — Stefan, Aug 08 '18 at 11:04
Agree with stefan, it is more about the actual groupings of the weekly clicks than just finding weeks. — andrelange91, Aug 08 '18 at 12:28

score 1 · Answer 1 · edited Aug 09 '18 at 16:35

You can make an extension for DateTime and then group by that method.

An extension I use (not quite sure, but I think I found it here on SO):

public static int? GetIso8601WeekOfYear(this DateTime? dt)
{
    if (!dt.HasValue) return null;

    // Seriously cheat.  If its Monday, Tuesday or Wednesday, then it'll 
    // be the same week# as whatever Thursday, Friday or Saturday are,
    // and we always get those right
    DayOfWeek day = CultureInfo.InvariantCulture.Calendar.GetDayOfWeek(dt.Value);
    if (day >= DayOfWeek.Monday && day <= DayOfWeek.Wednesday)
    {
        dt = dt.Value.AddDays(3);
    }

    // Return the week of our adjusted day
    return CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(dt.Value, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
}

But be aware, like Stefan mentioned in a comment above, you need to check which standard you need. You might to write a different extension method.

Update: extension method is from this post: Get the correct week number of a given date

Upvotes for @il_guru please ;)

Stefan · Accepted Answer · 2018-08-08T11:05:26.363

1

So, something like:

DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo;
Calendar cal = dfi.Calendar;

//just to make formatting on SO better
var whereClause = ClickStatistics
         .Where(i => i.LogDate.Year == selectedYear && i.Statement.Deleted == false).ToList();

var WeekCountQuery = from c in whereClause
                     group c by 
                     cal.GetWeekOfYear(c.LogDate, dfi.CalendarWeekRule, dfi.FirstDayOfWeek) 
                     into grp
                     select new
                     {
                          Week = grp.Key,
                          Clicks = grp.Count(),
                     };

Or of course one of the other extension methods mentioned in the comments.

e.g.:

var WeekCountQuery = from c in whereClause
                     group c by 
                     //make sure you have the extension method
                     c.LogDate.GetIso8601WeekOfYear() 
                     into grp
                     select new
                     {
                          Week = grp.Key,
                          Clicks = grp.Count(),
                     };

edited Aug 08 '18 at 11:05

answered Aug 08 '18 at 10:58

Stefan

17,448
11
60
79

What and how do i get the extension method ? i can get the top one to work so far. But not the bottom one because of the extension. Is it like a nuget packet ? – andrelange91 Aug 08 '18 at 11:25
Hi, no it's in this link: https://stackoverflow.com/questions/11154673/get-the-correct-week-number-of-a-given-date#11155102 – Stefan Aug 08 '18 at 11:26
But: make sure you don't run it against the sql database ... it wouldn't understand it. That's why: the `ToList()` call is done first. – Stefan Aug 08 '18 at 11:28
This would be purely used for displaying the data. Not inserting – andrelange91 Aug 08 '18 at 11:29
Yes, I understand. I just want to make sure that you know, what although we are only selecting data, extension methods like `GetIso8601WeekOfYear` don't translate to a SQL statement. It needs to be run against a in-memory collection, hence the ToList to run the sql select query first. – Stefan Aug 08 '18 at 11:31
Otherwise you'll get these kind of errors: https://stackoverflow.com/questions/2556790/cannot-translate-to-sql-using-selectx-funcx – Stefan Aug 08 '18 at 11:32
I will mark as answer as soon as i have tested it and it works for me, will mark shortly. – andrelange91 Aug 08 '18 at 11:46
btw. how would i in the top solution, order it by ascending on week numbers? would i do that on the grp.week or ? – andrelange91 Aug 08 '18 at 11:47
1

Thanks, In this case, I think it's best to order by after the grouping and selecting. Otherwise, I think, you need to run the week determin-method again on the data set. – Stefan Aug 08 '18 at 11:50

Calculate statistics for number of clicks per week

2 Answers2