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?

[sandeep-singh]

Hi @My_Rebecca, you have to create a master calendar with the available date fields. There are a lot of community post on how to create a master calendar. You can take a reference from below post.

https://community.qlik.com/t5/QlikView-App-Dev/How-to-Create-Master-Calendar/td-p/103109

[My_Rebecca]

Dera @sandeep-singh , what does PEEK mean? What's the meaning of following expression?

 Let vMinDate = Peek('MinDate',0,'Min_Max');

 Let vMaxDate = Peek('MaxDate',0,'Min_Max');

I notice you have used it in my previous post, please let me know, thanks.

[My_Rebecca]

Dear @sandeep-singh , I also try to scrip as following, but error occurs, how to correct it?

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);

Productivity_Min_Max:
Load
Min("DN Creation Date") as "Productivity_MinDate",
Max("DN Creation Date") as "Productivity_MaxDate"
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);

Resident Productivity_Min_Max;

Let vProductivity_MinDate = Peek('Productivity_MinDate',0,'Productivity_MinDate');
Let vProductivity_MaxDate = Peek('Productivity_MaxDate',0,'Productivity_MaxDate');

Tab1:
LOAD "Shipping Point" as "Shipping Point1",

[sandeep-singh]

The peek() function is most frequently used to locate the first or last value of a particular field, which are the relevant boundaries in a previously loaded table.

Load Min(Date) as MinDate, Max(Date) as MaxDate Fills the table's MinDate and MaxDate columns with the minimum and maximum dates. (Only 1 record is loaded into the MinMaxDate table)

Let vMinDate = Peek('MinDate',0,'Min_Max'); // Peek assigns the variable the Minimum date it can return from the MinMaxDate table.
Let vMaxDate = Peek('MaxDate',0,'Min_Max'); // Peek assigns the variable the maximum date it can return from the MinMaxDate table.

For Script, try this

Productivity_Min_Max:
Load
Min("DN Creation Date") as "Productivity_MinDate",
Max("DN Creation Date") as "Productivity_MaxDate"
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);

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

DROP TABLE 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 , there are many excel sheets in this path: [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/KPI Dashboard/DC TJ/GI/DC TJ GI_*.XLSX] . I tried your script, only the first excel sheet is loaded (Min: 2021.10.01, Max: 2021.10.30).

How to enable all sheets loaded?

[sandeep-singh]

Quick Question: Are you already loading these files in your app for other work? Or only loading to create a master calendar. If you are already loading it, then you have to do the resident load of that table else you have to load all files first then do the resident load for master calendar.

[My_Rebecca]

Dear @sandeep-singh , these excel sheets would be loaded not only for the Min/Max Date or calendar purpose, but also for other calculation purpose. But these sheets would be used only once while loading in Section GI, no other sections would be used. Hope you understand and please support to solve this issue.

[My_Rebecca]

Another point is that I could not load all the files as the files qty will increase monthly.

[sandeep-singh · Accepted solution]

Hi @My_Rebecca , As I said earlier, you have to load all files first then do the resident table.

Try this

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 vProductivity_MinDate = Peek('Productivity_MinDate', 0, 'Productivity_Min_Max');
Let vProductivity_MaxDate = 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],
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;