Penyelesaian LuckyTemplates Antara Tarikh Waktu Kerja

Dalam blog hari ini, saya akan menunjukkan pendekatan kepada Cabaran Berhenti Permulaan Access Analytic di mana, menggunakan LuckyTemplates, jumlah jam kerja antara tarikh akan dikira. Anda boleh menonton video penuh tutorial ini di bahagian bawah blog ini .

Tugasnya adalah untuk mengira jumlah jam bagi seorang kakitangan pada setiap bulan dan tarikh. Saya membentangkan tarikh mula dan tamat yang boleh merentasi beberapa bulan dan mengambil kira status menangani kemungkinan kesilapan menaip. 

Isi kandungan

• Set Data Untuk LuckyTemplates Antara-Tarikh Waktu Kerja
• Menapis Baris
• Menambah Lajur Tersuai
• LuckyTemplates Antara-Tarikh Waktu Kerja: Pembolehubah
• LuckyTemplates Antara-Tarikh Waktu Kerja: Unjuran
• Kuasa BI Antara-Tarikh Waktu Kerja: Nilai Agregat
• Kesimpulan

Set Data Untuk LuckyTemplates Antara-Tarikh Waktu Kerja

Mari pergi ke editor Power Query. 

Berikut ialah data kami untuk cabaran ini. Kami mempunyai jadual dengan lajur untuk Nama Kakitangan, Tarikh Mula, Masa Mula, Tarikh Berhenti, Masa Berhenti dan Status.

Menapis Baris

Mari mulakan dengan memilih baris berdasarkan statusnya dan editor pertanyaan boleh membantu kami dengan logik ini. Klik anak panah lungsur di sebelah Status. Pilih Penapis Teks dan pilih Tidak Bermula Dengan. 

Dalam tetingkap Baris Penapis yang akan dipaparkan, tulis "e". Kemudian klik OK .

Tiada apa-apa akan berlaku lagi dan itu tidak mengapa kerana kami menulis huruf kecil "e" dan "Kecualikan" dalam data kami yang bermula dengan huruf besar "E".

Untuk membetulkannya, kami akan menggunakan parameter ketiga pilihan Text.StartsWith . Dalam bar formula, pergi ke penghujung fungsi Text.StartsWith. Tambahkan koma dan mari masukkan Comparer.OrdinalIgnoreCase kami. Klik tanda semak dan itu sepatutnya mengalih keluar baris Kecualikan .

Sudah tentu, kita mungkin mempunyai seseorang yang telah tersilap menaip "Kecualikan" tanpa "E" awalan. Dalam kes itu, kita boleh mencipta beberapa logik tambahan untuk mengikuti situasi tersebut. 

Kami boleh menguji jika perkataan itu tidak termasuk "X" dengan melanjutkan fungsi kami dan menggunakan Teks.Mengandungi . Untuk melakukan itu, tambah atau Text.Contains ([Status], “x”, Comparer.OrdinalIgnoreCase .

Perhatikan bahawa berbanding klausa pertama, kami menggantikan "e" dengan "x." Kami terus mengabaikan kes itu, dan membungkus satu set kurungan di sekeliling kedua-dua klausa kami. Klik tanda semak dan kita harus mendapatkan jadual tanpa baris yang mengandungi status Kecualikan atau Xtermasuk .

Menambah Lajur Tersuai

Sekarang mari kita menuju ke bahagian utama cabaran ini. Kami akan menambah lajur tersuai menggunakan UI dan kemudian bertukar kepada editor. 

Mulakan dengan ikon meja mini di penjuru kiri sebelah atas jadual. Klik padanya dan dalam menu lungsur, pilih Tambah Lajur Tersuai . 

Pada ketika ini, kami hanya berminat untuk membawa data yang kami perlukan nanti. Dan kerana kita akan bekerja dengan berbilang medan, mari kita cipta rekod menggunakan pemula rekod, diwakili dengan kurungan segi empat sama. 

Mari buat pembolehubah dan tetapkan lajur yang tersedia. Mulakan dengan menaip SD yang akan kita samakan dengan Tarikh Mula. Kita boleh melakukannya dengan mengklik Tarikh Mula pada lajur di sebelah kanan.

Kami akan melakukan perkara yang sama untuk pembolehubah ST, ED, dan ET untuk ditetapkan masing-masing kepada Masa Mula, Tarikh Berhenti dan Masa Berhenti . Kemudian, tutup rekod dengan kurungan penutup. Klik OK.

Lajur tersuai dengan rekod akan ditambahkan pada jadual. Klik pada ruang putih di sebelah Rakam dan anak tetingkap pratonton akan dibuka. Ini akan membawa masuk semua nilai dari baris ini.

LuckyTemplates Antara-Tarikh Waktu Kerja: Pembolehubah

Kami kemudiannya boleh memformatkan pembolehubah kami melalui tetingkap Advanced Editor . Beginilah rupa tetingkap Editor Lanjutan. 

Kemudian, mari letakkan pembolehubah kita dalam baris baharu yang berasingan.

Sekarang, kita boleh mengembangkan logik yang kita perlukan. Jadi, bukannya rekod, kita juga boleh menggunakan ungkapan biarkan bersarang di sini. Ambil perhatian bahawa salah satu daripada mereka akan berfungsi kerana kami mempunyai berbilang pembolehubah atau nama medan dengan nilai yang diberikan kepada mereka.

Tarikh Mula kami pada masa ini ditulis sebagai nilai teks dan kami perlu mengubahnya menjadi format tarikh yang betul. Untuk melakukan itu, bungkus fungsi Date.FromTeks di sekelilingnya. 

Untuk Masa Mula yang juga diformatkan sebagai rentetan, kita perlu menukarnya kepada nombor. Kemudian, kita boleh membahagikannya dengan seratus dan membundarkannya untuk mengalih keluar sebarang tempat perpuluhan. 

Menggunakan fungsi Number.From , bahagikan nilai Tarikh Mula kami dengan 100 . Kemudian, bungkusnya di dalam fungsi seterusnya, Number.Round , dan tambah , 0 pada penghujung untuk membundarkan tanpa tempat perpuluhan. 

Untuk Tarikh Tamat dan Masa Tamat, kami hanya akan menyalin fungsi yang sama yang kami ada dalam Tarikh Mula dan Masa Mula masing-masing, dan menggantikannya dengan pembolehubah yang sesuai.

Setelah pembolehubah diformat dengan betul, cipta medan lain dalam rekod kami untuk beberapa logik tambahan. Buat baris baharu dengan menekan Enter. 

Kita perlu membina senarai tarikh dari tarikh pertama sehingga tarikh tamat. Kami akan memanggil tarikh tersebut LD dan kami akan menggunakan fungsi List.Dates . 

Parameter pertama fungsi ini ialah permulaan sebagai tarikh yang akan menjadi SD kami. Kemudian, ia mahu kiraan sebagai nombor atau perbezaan antara tarikh mula dan tarikh tamat. Kita boleh mendapatkannya menggunakan fungsi Number.From diikuti dengan ( ED – SD) + 1 . Ambil perhatian bahawa "+1" tidak ditunjukkan dalam set tangkapan skrin seterusnya, tetapi harus ada +1 .

Parameter ketiga dipanggil langkah sebagai tempoh dan kami mahukan kenaikan satu hari. Kita boleh mendapatkannya menggunakan Tempoh. Daripada (1). 

Kami telah membuat senarai dengan tarikh dari tarikh mula pertama hingga tarikh berhenti. Apa yang kita mahu seterusnya ialah membuat senarai masa yang sesuai dengannya. 

Mari hubungi LT ini untuk senarai masa. LT boleh mempunyai tempoh hari dalam satu hari yang memerlukan logik yang sedikit berbeza berbanding jika ia merentasi beberapa hari. Jadi itu sesuatu yang kita kena buat syarat. 

Kami mahu syarat menyatakan "jika tarikh mula kami sama dengan tarikh tamat kami, maka masa tamat tolak masa mula." Hasilnya hendaklah dalam senarai format supaya kami menggunakan pemula senarai yang diwakili oleh { } . 

Sekarang, jika kita mempunyai julat tarikh yang menjangkau beberapa hari, maka tarikh pertama ialah 24 tolak masa mula. Kami akan menambah ini pada keadaan kami sebagai pernyataan else . Sekali lagi, formatkannya sebagai senarai menggunakan kurungan kerinting.

Pada ketika ini, kami belum mengambil kira sepanjang hari yang lain jadi kami akan menggunakan simbol ampersand (&) untuk menambahkan berbilang senarai. Untuk setiap hari sepanjang hari, buat senarai dengan nilai 24 yang mewakili 24 jam yang kita ada dalam sehari. Kami akan menggunakan List.Repeat untuk itu. 

Dengan fungsi List.Repeat , buat senarai yang mengandungi 24 dan ulangi beberapa kali dengan mengira bilangan hari dalam LD . Untuk mencapainya, gunakan List.Count (LD)   kemudian tolak 2 kerana kami mempunyai senarai berasingan untuk tarikh mula kami dan kami akan membuat senarai lain untuk masa tamat. 

Pada dasarnya, ini akan membuat senarai dengan hanya 24 jam untuk setiap hari sepanjang hari. 

Untuk masa akhir, kita boleh menambahkannya semula sebagai senarai menggunakan ampersand, Kemudian, menggunakan pemula senarai, mari kita panggil ET. 

Setakat ini, kami mempunyai dua senarai besar—senarai tarikh dan senarai masa—dengan panjangnya adalah sama antara satu sama lain. Daripada dua senarai tersebut, kita boleh membina satu jadual. 

Dalam baris baharu, buat pembolehubah lain untuk jadual yang akan kita panggil t dan gunakan fungsi Table.FromColumns . Fungsi ini memerlukan senarai sebagai senarai dan kami akan menggunakan LD kami. 

LD kami mengandungi tarikh individu dari tarikh mula hingga tarikh berhenti. Kita boleh menukar senarai tarikh itu kepada nilai tarikh akhir bulan dengan menghantar LD dan memanggil fungsi Date.EndOfMonth . Kemudian di baris seterusnya, mari dapatkan LT kami juga. 

Tekan Enter untuk mencipta baris lain dan tetapkan jadual kami sebagai jadual jenis . Jadual kami akan mempunyai dua lajur, lajur pertama ialah lajur tarikh. Jadi, buat lajur Tarikh dan panggil fungsi Date.Type .

Lajur kedua akan menjadi lajur Jam kami dan ia akan menjadi integer. Oleh itu, kami akan mencipta lajur Hrs dan memanggil Int8.Type.

Kami mempunyai kurungan penutup untuk menutup rekod kami dan kurungan penutup untuk menutup fungsi Table.AddColumn kami. Klik Selesai dan kami mendapat senarai rekod di bawah lajur Tersuai. 

Sekali lagi, klik pada ruang putih di sebelah setiap Rekod dan jadual harus dibuka di bahagian bawah skrin. 

Perhatikan bahawa kita mempunyai t Jadual di baris terakhir. Mari cuba mendalami salah satu Rekod. Klik kanan pada hasil pertama dan Tambah sebagai Pertanyaan Baru . 

Kami akan mendapat ringkasan yang sama seperti sebelumnya dan jika kami mengklik pada Jadual , kami sepatutnya mendapat nilai Tarikh dan Jam kami.

Tetapi kami amat berminat dengan jadual keluaran akhir. Untuk memfokuskan pada itu, mari kita alih keluar pertanyaan bantuan dengan mengklik kanan Tersuai dan memilih Padam . Sahkan dengan mengklik Padam dalam tetingkap pop timbul.

Setelah dipadamkan, kami boleh kembali ke pertanyaan awal kami dan sekali lagi mengklik ruang putih di sebelah Rekod. Ia akan menunjukkan hasil yang sama seperti sebelumnya. Tetapi kali ini, mari kita kembangkan bar formula dan panggil [t] di sebelah kurungan penutup kita.

Dalam lajur Tersuai, kita dapat melihat bahawa setiap Rekod ditukar kepada Jadual . 

LuckyTemplates Antara-Tarikh Waktu Kerja: Unjuran

Daripada jadual yang kami lihat, hanya terdapat dua lajur yang diminati—nama kakitangan dan lajur tersuai kami. Kita boleh menggunakan unjuran untuk mengekalkan dua lajur itu sahaja daripada jadual ini. 

Pergi ke bar formula, dan di sebelah kurungan penutup kami, pilih medan yang ingin kami simpan dengan meletakkannya di dalam kurungan segi empat sama. Kemudian tekan tanda semak.

Kita sepatutnya mendapat keputusan ini.

Kita boleh mengembangkan jadual bersarang dengan mengklik pada ikon anak panah  di sebelah Custom . Nyahtanda Gunakan nama lajur asal sebagai awalan dan klik OK.

Meja kami sepatutnya kelihatan seperti ini.

Kuasa BI Antara-Tarikh Waktu Kerja: Nilai Agregat

Apa yang perlu dilakukan sekarang ialah mengagregatkan nilai-nilai ini. Untuk berbuat demikian, pilih lajur Nama Kakitangan dan pergi ke Transform. 

Dalam tetingkap timbul, pilih Jam di bawah Lajur Nilai kerana ia adalah perkara yang ingin kita agregatkan. Kemudian, pilih Jumlah di bawah Fungsi Nilai Agregat . Kemudian klik OK.

Output akhir kami akan kelihatan seperti ini. 

Mencipta Jadual Tarikh Dalam LuckyTemplates Cara Terpantas Kemungkinan
Perbandingan Masa Untuk Jadual Tarikh Bukan Standard Dalam LuckyTemplates
DateTime Nilai: Cara Menanggalkan Saat

Kesimpulan

Dalam blog ini, anda telah melihat satu pendekatan untuk Cabaran Berhenti Permulaan Access Analytic. Anda mempelajari cara mengira jumlah jam bekerja oleh setiap kakitangan dalam sebulan dan cara menangani kemungkinan kesilapan dalam data anda. 

Dengan teknik ini dan LuckyTemplates, waktu kerja antara tarikh boleh dikira dengan mudah dalam organisasi anda sendiri, atau sebagai amalan untuk mendalami pengetahuan dan kemahiran LuckyTemplates anda.