Help:To build report

[manojkulkarni]

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

[varshavig12]

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)

[Anonymous]

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