Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
I am looking for solution/suggestion in building weekly report using below sample data.
Requirement:
We have Activity details captured on daily basis. Activity can be started & completed on same day or it might carry forwarded to next day. I need to prepare a weekly report on this data to show how many activities received and completed in a week. I am not able to get the report for the cases which are started in week1 and closed in week2. How to achieve this ?
Sample Data
ActivityID | ActivityDesc | Start Date | End Date |
A01 | Test1 | 03-01-2017 | 03-01-2017 |
A02 | Test2 | 03-01-2017 | 05-01-2017 |
A03 | Test3 | 03-01-2017 | 10-01-2017 |
A04 | Test4 | 04-01-2017 | 06-01-2017 |
A05 | Test5 | 04-01-2017 | 04-01-2017 |
A06 | Test6 | 05-01-2017 | 05-01-2017 |
A07 | Test7 | 06-01-2017 | 10-01-2017 |
A08 | Test8 | 03-01-2017 | 09-01-2017 |
A09 | Test9 | 09-01-2017 | 09-01-2017 |
A10 | Test10 | 10-01-2017 | 12-01-2017 |
Expected Out put
Started | Closed | |
Week1 (02 Jan to 6th Jan 2017) | 8 | 5 |
Week2 (09 Jan to 13th Jan 2017) | 2 | 5 |
Try this:
a:
Load ActivityID ,ActivityDesc ,date#([Start Date],'DD-MM-YYYY') as StartDate ,date#([End Date],'DD-MM-YYYY') as EndDate,
Right(WeekName(date#([Start Date],'DD-MM-YYYY')),2) as WeekS,
Right(WeekName(date#([End Date],'DD-MM-YYYY')),2) as WeekC
;
LOAD * INLINE [
ActivityID ,ActivityDesc ,Start Date ,End Date
A01 ,Test1 ,03-01-2017 ,03-01-2017
A02 ,Test2 ,03-01-2017 ,05-01-2017
A03 ,Test3 ,03-01-2017 ,10-01-2017
A04 ,Test4 ,04-01-2017 ,06-01-2017
A05 ,Test5 ,04-01-2017 ,04-01-2017
A06 ,Test6 ,05-01-2017 ,05-01-2017
A07 ,Test7 ,06-01-2017 ,10-01-2017
A08 ,Test8 ,03-01-2017 ,09-01-2017
A09 ,Test9 ,09-01-2017 ,09-01-2017
A10 ,Test10 ,10-01-2017 ,12-01-2017
];
b:
Mapping Load ActivityID,WeekC
resident a;
Load ActivityID ,ActivityDesc, 'C' as flag,
applymap('b',ActivityID) as Week
Resident a;
c:
Mapping Load ActivityID,WeekS
resident a;
Load ActivityID ,ActivityDesc, 'S' as flag,
applymap('c',ActivityID) as Week
Resident a;
d:
load ActivityID,StartDate,EndDate
Resident a;
drop table a;
Expression: count({<flag={'S'}>}ActivityID)
count({<flag={'C'}>}ActivityID)
Hi Manoj,
Please find following:
Change In Script:-
RawData:
LOAD * INLINE [
ActivityID, ActivityDesc, Start Date, End Date
A01, Test1, 03-01-2017, 03-01-2017
A02, Test2, 03-01-2017, 05-01-2017
A03, Test3, 03-01-2017, 10-01-2017
A04, Test4, 04-01-2017, 06-01-2017
A05, Test5, 04-01-2017, 04-01-2017
A06, Test6, 05-01-2017, 05-01-2017
A07, Test7, 06-01-2017, 10-01-2017
A08, Test8, 03-01-2017, 09-01-2017
A09, Test9, 09-01-2017, 09-01-2017
A10, Test10, 10-01-2017, 12-01-2017
];
mainData:
load * ,
WeekStart(date#([Start Date],'DD-MM-YYYY')) AS Start_Week,
WeekStart(date#([End Date],'DD-MM-YYYY')) as End_Week,
WeekStart(date#([Start Date],'DD-MM-YYYY')) as Date1
Resident RawData;
DROP table RawData;
UI table:-
Start_Week | End_Week | Count(ActivityID) |
---|---|---|
1/2/2017 | 1/2/2017 | 5 |
1/9/2017 | 3 | |
Total | 8 | |
1/9/2017 | 1/9/2017 | 2 |
Total | 2 | |
Total | 10 |
Let me know if above is working for you
Thanks,
Pooja