LuckyTemplates Between Dates Arbeidstidsløsning

I dagens blogg vil jeg demonstrere en tilnærming til Access Analytics Start Stop Challenge hvor, ved hjelp av LuckyTemplates, vil den totale arbeidstiden mellom datoene bli beregnet. Du kan se hele videoen av denne opplæringen nederst på denne bloggen .

Oppgaven er å beregne totalt antall timer for en medarbeider på hver måned og dato. Jeg presenterte start- og sluttdatoer som kan gå over måneder og ta hensyn til status ved håndtering av mulige skrivefeil. 

Innholdsfortegnelse

• Datasett for LuckyTemplates Between-Dates Working Hours
• Filtrering av radene
• Legge til en egendefinert kolonne
• LuckyTemplates Between-Dates Arbeidstider: Variablene
• LuckyTemplates Between-Dates Arbeidstider: Projeksjon
• LuckyTemplates Between-Dates Arbeidstider: Samlede verdier
• Konklusjon

Datasett for LuckyTemplates Between-Dates Working Hours

La oss gå til Power Query-redigeringsprogrammet. 

Her er våre data for denne utfordringen. Vi har en tabell med kolonner for Personalnavn, Startdato, Starttid, Stoppdato, Stopptid og Status.

Filtrering av radene

La oss starte med å velge radene basert på statusen deres, og Query-editor kan hjelpe oss med denne logikken. Klikk på rullegardinpilen ved siden av Status. Plukke utog velg Begynner ikke med. 

Skriv "e" i Filter Rows- vinduet som vises . Klikk deretter OK .

Ingenting vil skje ennå, og det er greit fordi vi skriver en liten "e" og "Ekskluder" i dataene våre som starter med en stor "E".

For å fikse det, bruker vi den valgfrie tredje parameteren til Text.StartsWith . Gå til slutten av Text.StartsWith-funksjonen i formellinjen . Legg til et komma og la oss gå inn i vår Comparer.OrdinalIgnoreCase. Klikk på haken, og det bør fjerne Ekskluder- radene.

Selvfølgelig kan vi ha noen som feilaktig har skrevet "Ekskluder" uten en innledende "E." I så fall kan vi lage litt ekstra logikk for å følge situasjonen. 

Vi kan teste om ordet ikke inneholder en "X" ved å utvide funksjonen vår og bruke Text.Contains . For å gjøre det, legg til eller Text.Contains ([Status], "x", Comparer.OrdinalIgnoreCase .

Legg merke til at sammenlignet med den første klausulen, erstattet vi "e" med en "x." Vi fortsetter å ignorere saken, og vikler et sett med parenteser rundt begge klausulene våre. Klikk på haken og vi skal få en tabell uten radene som inneholder Exclude- eller Xclude -status.

Legge til en egendefinert kolonne

La oss nå gå til hoveddelen av denne utfordringen. Vi legger til en egendefinert kolonne ved hjelp av brukergrensesnittet og bytter deretter til redigeringsprogrammet. 

Start med minibordikonet i øvre venstre hjørne av bordet. Klikk på den og velg Legg til i rullegardinmenyen . 

På dette tidspunktet er vi bare interessert i å bringe dataene vi trenger senere. Og fordi vi skal jobbe med flere felt, la oss lage en post ved å bruke postinitialisatorene, representert med firkantede parenteser. 

La oss lage variabler og tildele de tilgjengelige kolonnene. Start med å skrive SD som vi tilsvarer startdatoen. Vi kan gjøre det ved å klikke Startdato i kolonnen til høyre.

Vi vil gjøre det samme for variablene ST, ED og ET som skal tildeles til henholdsvis starttid, stoppdato og stopptid . Lukk deretter posten med en avsluttende parentes. Klikk OK.

En egendefinert kolonne med postene vil bli lagt til i tabellen. Klikk på det hvite området ved siden av Record og en forhåndsvisningsrute åpnes. Dette vil hente inn alle verdiene fra denne raden.

LuckyTemplates Between-Dates Arbeidstider: Variablene

Vi kan deretter formatere variablene våre gjennom Advanced Editor- vinduet. Slik skal Advanced Editor-vinduet se ut. 

La oss deretter sette variablene våre i separate nye linjer.

Nå kan vi utvide logikken vi trenger. Så, i stedet for en post, kan vi også bruke et nestet let-uttrykk her. Merk at en av disse vil fungere fordi vi har flere variabler eller feltnavn med verdier tilordnet dem.

Startdatoen vår er for øyeblikket skrevet som en tekstverdi, og vi må transformere den til et riktig datoformat. For å gjøre det, vikle funksjonen Date.FromText rundt den. 

For starttiden som også er formatert som en streng, må vi konvertere den til et tall. Deretter kan vi dele den med hundre og runde den for å fjerne eventuelle desimaler. 

Ved å bruke Number.From- funksjonen deler du startdatoverdien med 100 . Pakk dem deretter inn i neste funksjon, Number.Round , og legg til , 0 på slutten for å runde av uten desimaler. 

For sluttdato og sluttid vil vi bare kopiere de samme funksjonene vi hadde i henholdsvis startdato og starttid , og erstatte dem med de riktige variablene.

Når variablene er riktig formatert, oppretter du et annet felt i posten vår for litt ekstra logikk. Opprett en ny linje ved å trykke Enter. 

Vi må konstruere en liste over datoer fra den første datoen og frem til sluttdatoen. Vi kaller disse datoene LD og vi bruker funksjonen List.Dates . 

Den første parameteren til denne funksjonen er start som dato som vil være vår SD. Deretter vil den telle som tall eller forskjellen mellom startdatoen og sluttdatoen. Vi kan få det ved å bruke Number.From- funksjonen etterfulgt av ( ED – SD) + 1 . Merk at "+1" ikke vises i de neste settene med skjermbilder, men det skal være en +1 .

Den tredje parameteren kalles trinnet som varighet og vi ønsker en økning på én dag. Vi kan få det ved å bruke Duration.From (1). 

Vi har laget en liste med datoer fra den aller første startdatoen til stoppdatoen. Det vi ønsker videre er å lage en liste over tider som følger med det. 

La oss kalle dette LT for listetider. LT kan ha en dagsperiode på en enkelt dag som krever en litt annen logikk enn om den spenner over flere dager. Så det er noe vi må legge til rette for. 

Vi vil at betingelsen skal si "hvis startdatoen vår er den samme som sluttdatoen, så sluttid minus starttidspunktet." Resultatene skal være i en liste med formater, så vi bruker listeinitialisereren representert av { } . 

Nå, hvis vi har en datoperiode som spenner over flere dager, vil den første datoen være 24 minus starttidspunktet. Vi vil legge dette til vår tilstand som den andre uttalelsen. Igjen, formater den som en liste med de krøllede parentesene.

På dette tidspunktet har vi ikke tatt hensyn til de andre hele dagene, så vi bruker og-tegnet (&) for å legge til flere lister. For hver hel dag, lag en liste med verdien 24 som representerer de 24 timene vi har på et døgn. Vi bruker List.Repeat for det. 

Med List.Repeat -funksjonen oppretter du en liste som inneholder 24 og gjentar det et antall ganger ved å telle antall dager i LD . For å oppnå det, bruk List.Count (LD)   og trekk fra 2 fordi vi har en egen liste for startdatoen vår, og vi lager en annen liste for slutttidspunktet. 

I hovedsak vil dette lage en liste med bare 24 timer for hver hel dag. 

For slutttiden kan vi legge den til igjen som en liste ved å bruke og-tegnet. Deretter, ved å bruke listeinitialisatorene, la oss kalle ET. 

Så langt har vi to store lister - en liste over datoer og en liste over tider - der lengdene er lik hverandre. Fra disse to listene kan vi konstruere en enkelt tabell. 

På en ny linje oppretter du en annen variabel for tabellen som vi kaller t og bruker funksjonen Table.FromColumns . Denne funksjonen krever lister som liste , og vi bruker vår LD. 

Vår LD inneholder individuelle datoer fra startdatoen til stoppdatoen. Vi kan transformere den listen over datoer til datoverdien for slutten av måneden ved å sende LD og kalle Date.EndOfMonth -funksjonen. Så i neste linje, la oss få vår LT også. 

Trykk Enter for å lage en ny linje og angi tabellen som typetabell . Tabellen vår vil ha to kolonner, den første kolonnen er en datokolonne. Så, lag en Dato- kolonne og kall opp Date.Type -funksjonen.

Den andre kolonnen vil være vår Timer- kolonne, og den vil være et heltall. Dermed oppretter vi en Hrs- kolonne og kaller Int8.Type.

Vi har den avsluttende parentesen for å lukke posten vår og en avsluttende parentes for å lukke vår Table.AddColumn -funksjon. Klikk på Ferdig og vi får en liste over poster under en egendefinert kolonne. 

Igjen, klikk på det hvite rommet ved siden av hver post , og bordet skal åpnes nederst på skjermen. 

Legg merke til at vi har vår t-tabell i siste rad. La oss prøve å bore i en av postene. Høyreklikk på det første resultatet og Legg til som ny spørring . 

Vi vil få samme oppsummering som tidligere, og hvis vi klikker på tabellen , bør vi få våre Dato- og Hrs -verdier.

Men vi er først og fremst interessert i den endelige produksjonstabellen. For å fokusere på det, la oss fjerne hjelpespørringen ved å høyreklikke Egendefinert og velge Slett . Bekreft ved å klikke på Slett i popup-vinduet.

Når den er slettet, kan vi gå tilbake til vår første spørring og igjen klikke på det hvite området ved siden av Record. Det vil vise de samme resultatene som tidligere. Men denne gangen, la oss utvide formellinjen og kalle [t] ved siden av våre avsluttende parenteser.

I Egendefinert-kolonnen kan vi se at hver post endres til Tabell . 

LuckyTemplates Between-Dates Arbeidstider: Projeksjon

Fra tabellen vi ser på, er det bare to kolonner av interesse – personalnavnet og vår egendefinerte kolonne. Vi kan bruke projeksjon for å beholde de to kolonnene alene fra denne tabellen. 

Gå til formellinjen, og ved siden av vår avsluttende parentes, velg feltene vi vil beholde ved å plassere dem innenfor firkantede parenteser. Trykk deretter på haken.

Vi bør få dette resultatet.

Vi kan utvide ved å klikke på pilikonene  ved siden av Custom . Fjern merket for Bruk originalt kolonnenavn som prefiks og klikk OK.

Tabellen vår skal se slik ut.

LuckyTemplates Between-Dates Arbeidstider: Samlede verdier

Alt som gjenstår nå er å samle disse verdiene. For å gjøre det, velg kolonnen Personalnavn og gå til Transform. 

I popup-vinduet velger du timer under verdikolonnen fordi det er det vi ønsker å samle. Deretter velger du Sum under . Klikk deretter OK.

Vår endelige produksjon vil se slik ut. 

Konklusjon

I denne bloggen har du sett én tilnærming til Access Analytics Start Stop Challenge. Du lærte hvordan du beregner det totale antallet timer som hver medarbeider har jobbet i løpet av en måned, og hvordan du håndterer mulige skrivefeil i dataene dine. 

Med denne teknikken og LuckyTemplates kan arbeidstiden mellom dato enkelt beregnes i din egen organisasjon, eller som en praksis for å utdype LuckyTemplates kunnskap og ferdigheter.