Productivity Calculation

[My_Rebecca]

Productivity = total items / (person*8 hours*working days).

Working days calculation, I choose NetWorkdays, but this expression needs start date and end date. 

Actually I do not have the exact start and end date, I just need to see weekly, monthly, quarterly, yearly or specific period by filter. How to realize it by expression or script?

[My_Rebecca]

Sorry, there is something wrong, could you please take a look at the script? 

GI:
load *,
"DN Creation Date" as [Productivity Date]
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/KPI Dashboard/DC TJ/GI/DC TJ GI_*.XLSX]
(ooxml, embedded labels, table is Sheet1);

Productivity_Min_Max:
Load
Min("DN Creation Date") as "Productivity_MinDate",
Max("DN Creation Date") as "Productivity_MaxDate"

Resident GI;

Let vProductivityMinDate = Peek('Productivity_MinDate', 0, 'Productivity_Min_Max');
Let vProductivityMaxDate = Peek('Productivity_MaxDate', 0, 'Productivity_Min_Max');

temp_DateField:
LOAD
$(vProductivity_MinDate) + rowno() -1 as Num,
date($(vProductivity_MinDate) + rowno() -1 ) as TempDate
AUTOGENERATE $(vProductivity_MaxDate) - $(vProductivity_MinDate)+1;

Productivity_Calendar:
LOAD
TempDate as [Productivity Date],
'WK' & num(Week(TempDate),'00') AS [Productivity Week],
'FY' & if(month(TempDate)>9,Right(Year(TempDate),2)*1+1,Right(Year(TempDate),2)) as [Productivity Year],
Year(TempDate) & '_' & num(Month(TempDate),'00') as [Productivity Month],
'Q' & if(month("TempDate")<4,2,if(month("TempDate")<7,3,if(month("TempDate")<10,4,1))) as [Productivity Quarter],
'P' & num(if(month("TempDate")<10,month("TempDate")+3,month("TempDate")-9),'00') as [Productivity Period]
Resident temp_DateField
ORDER BY TempDate ASC;

DROP TABLE temp_DateField;

GI:
Mapping Load
"Shipping Point",
"SP Description"
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/Parameter.xlsx]
(ooxml, embedded labels, table is Plant_SL_SP);

Tab1:
LOAD "Shipping Point" as "Shipping Point1",
Delivery as GI_Delivery,
"DN Creation Date",

[My_Rebecca]

I also do not understand the meaning of "AUTOGENERATE", what's the meaning?

[sandeep-singh]

Hi @My_Rebecca ,

Try this. If still encounter any issue th,en share me some dummy sample for validation.

GI:
load *,
"DN Creation Date" as [Productivity Date]
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/KPI Dashboard/DC TJ/GI/DC TJ GI_*.XLSX] (ooxml, embedded labels, table is Sheet1);

Productivity_Min_Max:
Load
floor(Min("DN Creation Date")) as "Productivity_MinDate",
floor(Max("DN Creation Date")) as "Productivity_MaxDate"
Resident GI;

Let vProductivity_MinDate = Peek('Productivity_MinDate', -1, 'Productivity_Min_Max');
Let vProductivity_MaxDate = Peek('Productivity_MaxDate', -1, 'Productivity_Min_Max');

temp_DateField:
LOAD
$(vProductivity_MinDate) + rowno() -1 as Num,
date($(vProductivity_MinDate) + rowno() -1 ) as TempDate
AUTOGENERATE $(vProductivity_MaxDate) - $(vProductivity_MinDate)+1;

Productivity_Calendar:
LOAD
TempDate as [Productivity Date],
Week(TempDate) AS [Productivity Week],
Year(TempDate) as [Productivity Year],
Month(TempDate) as [Productivity Month],
'Q'&Ceil(Month(TempDate)/3) as [Productivity Quarter],
Day(TempDate) as [Productivity Day],
QuarterName(TempDate) as [Productivity QuarterName]
Resident temp_DateField
ORDER BY TempDate ASC;


DROP TABLE temp_DateField;

[My_Rebecca]

Dear @sandeep-singh , the same error occurs. I attach 3 sheet for your test, thanks.

[sandeep-singh]

Hi @My_Rebecca, Thank you for the spreadsheets. I didn't encounter any error in my script through these files. Please make sure that same date format follows in all the files. One small change I make in the script is that I convert the variable in the number format and added trace to see if data is getting proper populated in the variable.

Try this one

GI:
load *,
"DN Creation Date" as [Productivity Date]
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/KPI Dashboard/DC TJ/GI/DC TJ GI_*.XLSX] (ooxml, embedded labels, table is Sheet1);

Productivity_Min_Max:
Load
floor(Min("DN Creation Date")) as "Productivity_MinDate",
floor(Max("DN Creation Date")) as "Productivity_MaxDate"
Resident GI;

Let vProductivity_MinDate = num(Peek('Productivity_MinDate', -1, 'Productivity_Min_Max'));
Let vProductivity_MaxDate = num(Peek('Productivity_MaxDate', -1, 'Productivity_Min_Max'));

Trace '$(vProductivity_MinDate)';
Trace '$(vProductivity_MaxDate)';

temp_DateField:
LOAD
$(vProductivity_MinDate) + rowno() -1 as Num,
date($(vProductivity_MinDate) + rowno() -1 ) as TempDate
AUTOGENERATE $(vProductivity_MaxDate) - $(vProductivity_MinDate)+1;

Productivity_Calendar:
LOAD
TempDate as [Productivity Date],
Week(TempDate) AS [Productivity Week],
Year(TempDate) as [Productivity Year],
Month(TempDate) as [Productivity Month],
'Q'&Ceil(Month(TempDate)/3) as [Productivity Quarter],
Day(TempDate) as [Productivity Day],
QuarterName(TempDate) as [Productivity QuarterName]
Resident temp_DateField
ORDER BY TempDate ASC;


DROP TABLE temp_DateField;

[My_Rebecca]

Dear @sandeep-singh , thanks for your reply. This time the scrip is loaded successfully, but how to use this variable correctly? I write the expression: Count(distinct(GI_Delivery & [GI_Delivery Item]))/(7*8*(NetWorkDays('$(=vProductivity_MinDate)','$(=vProductivity_MaxDate)')

But this expression does not work, the calculation result is incorrect while filtering. What is the reason?

The total productivity (1.6) are divided by FY22 & FY23 (1.1+0.5).  Obviously this is wrong. The correct result should be almost equal to 1.6 no matter which year, quarter, month are filtered.

[stevedark]

Hi @My_Rebecca 

Your variables seem to be set during the load script, so are therefore not aware of selections, this way you are dividing by all of the days regardless of how short a period you are looking at.

The variables should be something like:

vProductivity_MinDate

=DayStart(Min([DN Creation Date]))

And:

vProductivity_MaxDate

=DayStart(Max([DN Creation Date]))

You can create this via the variable dialog in sense, or in the load script using this code:

SET vProductivity_MinDate = =DayStart(Min([DN Creation Date]));
SET vProductivity_MaxDate = =DayStart(Max([DN Creation Date]));

This may not give exactly what you want when you start making selections though, as if you pick a single package it will give you the first and last dates that package appeared, ignoring days before and afterwards - this could increase productivity incorrectly.

The way around this is to use some set analysis, to ignore certain selections from the day start and end calculations, but keep others. Come back and let me know if this is required and you need some assistance.

Steve

https://www.quickintelligence.co.uk/blog/

[My_Rebecca]

Dear @stevedark , thanks for your reply. However I have tried your pointer before with similar variable setting, but not succeed. For example, when I filter Jan and Mar, the variables calculate from Jan 1st to Mar 31st (90 days), obviously the correct calculation should be from Jan 1st to Jan 31st plus from Mar 1st to Mar 31st (62 days).

I want to know how to improve it, only working for filtered period, look forward to your suggestion.

[stevedark]

Hi @My_Rebecca 

In that case working out the number of days between the first and the last will not work for you, as it will not take into account gaps in between dates.

You might therefore want to count distinct dates instead.

You can do that with the expression:

count(DISTINCT [Productivity Date])

In order to exclude weekends though you will need to load the Day field in to your calendar also, something like this:

LOAD
    TempDate as [Productivity Date],
    WeekDay(TempDate) as [Productivity Day],

The expression then becomes:

count(DISTINCT {<[Productivity Day]-={'Sat','Sun'}>}[Productivity Date])

Hope that helps get you a bit nearer.

Steve

Steve