Determination of workdays for week intervall

[TomBond77]

Hi experts

per record we have a start and an finish date.

With week(datefield) we can determine the starting and the finishing week.

Example: 28.06.2023 until 12.07.2023, starting week 26 and finishing week 28. 

Now we need to count the used working days for each week: week 26, 27 and 28.

How can I solve this issue?

Thanks, Tom

[udit_k · Accepted solution]

Use interval Match and create flag on trx column to count used working days.

Example:-

[Actual Data]:
LOAD

Name,
if(not isnull(Transaction),1,0) Flag
Week(Date) as WeekDays
FROM [lib://DataFiles/Actual Data.xlsx]
(ooxml, embedded labels, table is Sheet2);

Formatting:
LOAD
HEading,
"Start",
"End"
FROM [lib://DataFiles/Original format.xlsx]
(ooxml, embedded labels, table is Sheet2);

Inner Join IntervalMatch ( WeekDays )
LOAD "Start",
"End"
Resident Formatting;

Using Original format.xlsx ,We could customize count on dynamic week to show

[TomBond77]

thanks,

in your LOAD statement what exactly does the field "transaction" mean? We don't have any measures in this sceario.

Our records from source look like this: 

Does your statement "Week(Date) as WeekDays" with my scenario?

[udit_k]

--Transaction means to identify its a working day or not, if flag is zero than its not working day.

--Week (Date) as Weekdays represent to a combine column having value of both Start & End Date.

[TomBond77]

Thanks

I am not 100% sure about your logic. Can you please share those excel files?

I would like to understand more the Weekdays dimension and also the final IntervalMatch. Currently I am unsure how to solve my issue.

Thank you, Tom

[udit_k]

Hi , 

1.Data you are having has start and End week per record, as below:-

2.Now in raw data we should have Week wise data which need to be count, to find used working days.

[Actual Data]:
LOAD

Name,
if(not isnull(Transaction),1,0) Flag    // Flag to find count of used working days
Week(Date) as WeekDays    //Week wise data
FROM [lib://DataFiles/Actual Data.xlsx]
(ooxml, embedded labels, table is Sheet2);

/*********** Week START& END wise Dimension Data********************/

Formatting:
LOAD
HEading,
"Start",
"End"
FROM [lib://DataFiles/Original format.xlsx]
(ooxml, embedded labels, table is Sheet2);

/*************** To Determine working days in week interval****************/

Inner Join IntervalMatch ( WeekDays )
LOAD "Start",
"End"
Resident Formatting;

So by using flag =1, and splitting data in week interval (using interval match) we could achieve requirement.

[TomBond77]

Thanks

In my script I have already calculated the starting week and the finishing week, also the used working days. Therefore I have all relevant dimensions. I still have understanding problems to adopt your well provided solution. How do I get the used working days per week?

week(EventDate) as Week_Start,
Year(EventDate) as [Event Year],
Month(EventDate) as [Event Month],
date(EventDate) as [EventDate],
Text(Time(round( (EventDate+(2/24)), (2/24)), 'hh:mm')) as [Event Time],
if(Text(Time(round((EventDate+(2/24)), (2/24)), 'hh:mm'))='00:00', date(EventDate+1, 'DD.MM.YYYY'), date(EventDate)) as [Event Date],

week(EndDate) as Week_Finish,
EndDate as EndDate,
Text(Time(round( (EndDate+(2/24)), (2/24)), 'hh:mm'))as [Finish Time],
if(Text(Time(round((EndDate+(2/24)), (2/24)), 'hh:mm'))='00:00', date(EndDate+1, 'DD.MM.YYYY'), date(EventDate)) as [End Date],

NetWorkDays(if(Text(Time(round((EventDate+(2/24)), (2/24)), 'hh:mm'))='00:00', date(EventDate+1, 'DD.MM.YYYY'), date(EventDate)),
if(Text(Time(round((EndDate+(2/24)), (2/24)), 'hh:mm'))='00:00', date(EndDate+1, 'DD.MM.YYYY'), date(EventDate))) as [Work_Days],

[udit_k]

Follow interval match as example earlier and need to have count  of [work_days] aggr with respect to week.

The week is master calendar  week , which will spilt the count between start and end week as defined in data.

You could use Week (EventDate ) also as Master week.