Group Dates-By Week

Help Something Else
,
1

Hi I have a dt with columns date, order, cost etc
date(row wise)- 4/28/2021, 4/28/2021,…5/2/2021…5/14/2021.

I need to group this date by week. Like 4/28/2021 to 5/5/2021 will be week1. Next 7 days by week 2 etc

2

from row in raw_dt.AsEnumerable group row by
p=System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(CDate(row.Item("Date ")), System.Globalization.CalendarWeekRule.FirstFullWeek, DayOfWeek.Sunday).ToString into grp=Group Select p).ToList

3

I am getting 17 and 18 from here

4
5

I have grouped the dates now new dt will have non repetative dates. How I can find Min date and max date . Now can we grp this by dividing by 7

8

I don’t want week number

9

your request to group the date by week is changed?
Just tell us the indut sample data and the expected output and we will work out a suggestion based on this. Thanks

11

Sure

Input DT

Date Order Price Sales Price Profit
4/28/2021 4000 8400 4400
4/28/2021 5000 9000 4000
4/28/2021 6000 7400 1400
4/29/2021 2000 5500 3500
4/29/2021 3400 5000 1600
5/1/2021 8000 18000 10000
5/3/2021 4700 5200 500
5/3/2021 3200 4500 1300
5/3/2021 2900 8000 5100
5/3/2021 3000 8000 5000
5/4/2021 6000 15000 9000
5/4/2021 6400 12000 5600
5/5/2021 4300 7200 2900
5/6/2021 3800 9500 5700
5/8/2021 5200 12000 6800
5/8/2021 7300 10000 2700
5/8/2021 4200 8000 3800
5/10/2021 2100 5000 2900
5/12/2021 1100 2500 1400
5/14/2021 3400 5000 1600
5/15/2021 1800 2400 600
12

Excepted O/P DT

Item Week Order Price Sales Price Profit
1 4/28/2021-5/5/2021 58900 113200 54300
2 5/6/2021-5/12/2021 23700 47000 23300
3 5/11/2021-5/15/2021 5200 7400 2200
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
14

Thank you. Intial date it depends it is dynamic. I have solved it via tradational script. But I will look it into your query

18

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.