Group Dates-By Week

Help Something Else
,
13

Except of Item counter (we can handle later) we are close on it with following prototype LINQ

(From d In dtData
Let k=CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(CDate(d.Item("Date")), System.Globalization.CalendarWeekRule.FirstDay, DayOfWeek.Thursday)
Group d By k Into grp=Group
Let gdo = grp.Select(Function (x) x("Date").toString).OrderBy(Function (x) CDate(x)).toArray
Let ops = grp.Sum(Function (x) Convert.ToInt32(x("Order Price")))
Let sps = grp.Sum(Function (x) Convert.ToInt32(x("Sales Price")))
Let ps = grp.Sum(Function (x) Convert.ToInt32(x("Profit")))
Let wk = gdo.First() & "-"& gdo.Last()
Let ra = New Object(){"",wk,ops,sps,ps}
Select dtReport.Rows.Add(ra)).CopyToDataTable

grafik

But there are follwoing differences:
following your output we do see:

grafik
4/28/2021-5/5/2021 red
5/6/2021-5/12/2021 blue
5/11/2021-5/15/2021 around yellow

With some tricking the Weekstart was set on Thursday in the prototype. Refering to your described output it looks more to a weekstart on Wednesday. But in general we would maybe set a weekstart on Monday (on your code was set to Sunday).

Lets try to sort this out so the case can be finalized.

Also have a look here (e.g. for decomposing the LINQ into Hybrid approach: GroupBy:LINQ, Processing: Foreach GroupLists)

1 Like