Weekly Data Representation

[Ron1]

I have a data set where for a month daily level transaction is captured per Unit wise.

I need to segregate this data and show it in Weekly basis.

Input:

Unit	ID	Month	Date	Sales
Sales	201	6	6/29/2019 0:00	120
Sales	201	6	6/15/2019 0:00	122
Sales	201	6	6/22/2019 0:00	124
Sales	201	6	6/24/2019 0:00	126
Sales	201	6	6/19/2019 0:00	154
Finance	202	6	6/22/2019 0:00	182
Finance	202	6	6/11/2019 0:00	210
Finance	202	6	6/18/2019 0:00	238
Finance	202	6	6/15/2019 0:00	266
Finance	202	6	6/4/2019 0:00	294
Finance	202	6	6/25/2019 0:00	322
Finance	202	6	6/8/2019 0:00	350
HR	203	6	6/17/2019 0:00	378
HR	203	6	6/18/2019 0:00	406
HR	203	6	6/10/2019 0:00	434
HR	203	6	6/26/2019 0:00	462
HR	203	6	6/11/2019 0:00	490
HR	203	6	6/25/2019 0:00	518
HR	203	6	6/7/2019 0:00	546
ADMIN	204	6	6/27/2019 0:00	574
ADMIN	204	6	6/2/2019 0:00	602
ADMIN	204	6	6/9/2019 0:00	630
ADMIN	204	6	6/8/2019 0:00	658
ADMIN	204	6	6/1/2019 0:00	686
ADMIN	204	6	6/30/2019 0:00	714
ADMIN	204	6	6/17/2019 0:00	742
ADMIN	204	6	6/15/2019 0:00	770
ADMIN	204	6	6/3/2019 0:00	798
ADMIN	204	6	6/22/2019 0:00	826
ADMIN	204	6	6/29/2019 0:00	854
EXECUTIVE	205	6	6/1/2019 0:00	882
EXECUTIVE	205	6	6/8/2019 0:00	910
EXECUTIVE	205	6	6/20/2019 0:00	938
EXECUTIVE	205	6	6/16/2019 0:00	966
EXECUTIVE	205	6	6/13/2019 0:00	994
EXECUTIVE	205	6	6/23/2019 0:00	1022
EXECUTIVE	205	6	6/28/2019 0:00	1050
EXECUTIVE	205	6	6/25/2019 0:00	1078
EXECUTIVE	205	6	6/27/2019 0:00	1106

Output:

I require output in the below format. Kindly help

Week will be start from first wednesday of the month and will continue like this till last tuesday of that month.

Please help me to build a dynamic solution. THis is only one month data

Unit	Week Ending	Sum(Sales)
Sales	11-Jun	0
Sales	18-Jun	122
Sales	25-Jun	278
Finance	11-Jun	SO ON
Finance	18-Jun	SO ON
Finance	25-Jun	SO ON
SO ON	SO ON	SO ON
SO ON	SO ON	SO ON
SO ON	SO ON	SO ON
SO ON	SO ON	SO ON
SO ON	SO ON	SO ON
SO ON	SO ON	SO ON
SO ON	SO ON	SO ON

[Taoufiq_Zarra]

Hi,

why not IntervalMatch ?

I propose the following scenario:

1- in the script (and this is the most not geek part)
create a calendar which you specify the Weeks Ending (because a week that ends on Wednesday does not make the task any easier).
the script is in the form:

WeekEnd:
LOAD Date#(Start,'MM/DD/YYYY') as Start_New,Date#(End,'MM/DD/YYYY') as End_New, * INLINE [
Start , End, Week Ending
06/05/2019, 06/11/2019, 11-Jun
06/12/2019, 06/18/2019, 18-Jun
06/19/2019, 06/25/2019, 25-Jun

];

then an intervalMatch with your data,

So the script will look like this:

Event:
LOAD Date#(Date,'MM/DD/YYYY') as Date_New,* INLINE [
    Unit, ID, Month, Date, Sales
    Sales, 201, 6, 6/29/2019, 120
    Sales, 201, 6, 6/15/2019, 122
    Sales, 201, 6, 6/22/2019, 124
    Sales, 201, 6, 6/24/2019, 126
    Sales, 201, 6, 6/19/2019, 154
    Finance, 202, 6, 6/22/2019, 182
    Finance, 202, 6, 6/11/2019, 210
    Finance, 202, 6, 6/18/2019, 238
    Finance, 202, 6, 6/15/2019, 266
    Finance, 202, 6, 6/4/2019, 294
    Finance, 202, 6, 6/25/2019, 322
    Finance, 202, 6, 6/8/2019, 350
    HR, 203, 6, 6/17/2019, 378
    HR, 203, 6, 6/18/2019, 406
    HR, 203, 6, 6/10/2019, 434
    HR, 203, 6, 6/26/2019, 462
    HR, 203, 6, 6/11/2019, 490
    HR, 203, 6, 6/25/2019, 518
    HR, 203, 6, 6/7/2019, 546
    ADMIN, 204, 6, 6/27/2019, 574
    ADMIN, 204, 6, 6/2/2019, 602
    ADMIN, 204, 6, 6/9/2019, 630
    ADMIN, 204, 6, 6/8/2019, 658
    ADMIN, 204, 6, 6/1/2019, 686
    ADMIN, 204, 6, 6/30/2019, 714
    ADMIN, 204, 6, 6/17/2019, 742
    ADMIN, 204, 6, 6/15/2019, 770
    ADMIN, 204, 6, 6/3/2019, 798
    ADMIN, 204, 6, 6/22/2019, 826
    ADMIN, 204, 6, 6/29/2019, 854
    EXECUTIVE, 205, 6, 6/1/2019, 882
    EXECUTIVE, 205, 6, 6/8/2019, 910
    EXECUTIVE, 205, 6, 6/20/2019, 938
    EXECUTIVE, 205, 6, 6/16/2019, 966
    EXECUTIVE, 205, 6, 6/13/2019, 994
    EXECUTIVE, 205, 6, 6/23/2019, 1022
    EXECUTIVE, 205, 6, 6/28/2019, 1050
    EXECUTIVE, 205, 6, 6/25/2019, 1078
    EXECUTIVE, 205, 6, 6/27/2019, 1106
];

WeekEnd:
LOAD Date#(Start,'MM/DD/YYYY') as Start_New,Date#(End,'MM/DD/YYYY') as End_New, * INLINE [
Start , End, Week Ending
06/05/2019, 06/11/2019, 11-Jun
06/12/2019, 06/18/2019, 18-Jun
06/19/2019, 06/25/2019, 25-Jun

];
Inner Join IntervalMatch ( Date_New )
 
LOAD Start_New, End_New
Resident WeekEnd;

And the result :

for '-' values, it's because my calendar is not complete

Regards,

[Ron1]

I just use date function in the load statement. A simple one line solution

Date(WeekEnd("Date"),'DD-MMM') as WeekEndDate

It works like a charm