Hello everybody,
I would be very happy to help you with a problem.
I have an order distribution (movement table as test.xlsx in the attachment) in Excel. It shows the orders and their progress. However, date and time in separate fields or information.
About a reference date I want to count the daily orders, e.g. how many each area or sub-area existed (This solution comes from this post: Simultaneous orders).
But now I want to consider the layers as well. For example, how many orders per day (reference date) before 8 o'clock, and after 22 o'clock.
Since the table in the appendix is only one location and the areas or subregions have different shift times, I would like to use variables or a mapping table (also in the appendix), as all tables from the different locations are structured identically. The shift times can sometimes be divided into three parts. A distinction should also be made between weekdays (Mon-Fri & Sat and Sun) and public holidays.
Am open for solutions in the script or in the layout ;-D
I hope I could describe my problem reasonably 😉
Thank you very much for your interest and suggestions or proposed solutions!
Here is my previous script:
Auftrag:
LOAD Auftragnummer,
[Teil-Auftragnummer],
date(von) as Startdatum,
[von-Zeit],
date(bis) as Enddatum,
[bis-Zeit],
Jahr,
Monat,
[KW-Jahr],
KalWoche,
Abteilung,
Typ,
Typ1,
Bereich,
Subbereich,
AutoNumberHash256(Auftragnummer,von,bis) as Key
FROM
(ooxml, embedded labels, table is Liste);
ReferenzDatum:
LOAD distinct Key, Auftragnummer as Auftragnummer_Referenz,
Date(Startdatum+iterno()-1) as ReferenzDatum,
AutoNumberHash256(FallID,Startdatum+iterno()-1) as DauerCount
Resident Auftrag
WHILE Startdatum+iterno()-1 <= Enddatum;
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
Date(Floor(Min(ReferenzDatum))) as minDate,
Date(Floor(max(ReferenzDatum))) as maxDate
Resident ReferenzDatum;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS ReferenzDatum,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Here again the goals:
- show how many orders per sub-area were in progress at the same time
- divided by time corridors per weekday / holiday
- Either with variables for the shift times or via the mapping table.
