Pivot Table Slicer Tanggal dan Bulan

[Bayu Prasaja]

Assalamu'alaikum Para Suhu

Mohon Maaf mengganggu waktu weekend nya

Saya baru belajar pivot table dan mencoba membuat summary data mengguanakan pivot yang ujungnya nanti saya mau buat dashboard di excel, saya kurang paham penggunaan slicer dan timeline pada pivot. Permasalahan yang saya hadapi adalah :

*Data hanya bisa difilter untuk bulan Juni saja, ketika dipilih  di Timeline(bulan) Juli data tidak ada yang terfilter

* Ketika Timeline (Bulan) dipilih bulan Juli, data tidak terfilter pada bulan juli, begitu juga slicer nya ada tulisan "All values are filtered out"

* Slicer tanggal tidak otomatis update berdasarkan Timeline (bulan) yang dipilih, harus di refresh dulu di slicer nya

Result yang diinginkan :

* entah pakai timeline atau slicer untuk filter column Bulan yang intinya ada tools untuk memilih Bulan dan Tanggal, jika slicer/timeline yang sedang aktif juni, maka ketika slicer tanggal dpilih (misal tanggal 2) maka data terfilter tanggal 2 Juni
* ketika bulan dipilih maka slicer tanggal langsung update di bulan tersebut (tanpa refresh), secara view nanti di tanggal yang ada datanya dibulan tersebut dia ada perbedaan warna yang lebih menonjol dibanding yang tidak ada datanya

Notes:
saya ada sedikit pengetahuan tentang vba, jadi jika harus menggunakan vba untuk metode tersebut InsyaAllah bisa saya ikuti

Berikut saya lampirkan file data.xlsm agar lebih mudah komunikasi. 

Saya ucapkan terima kasih sebelumnya. Sukses selalau para suhu

[Mr. Kid]

Wa'alaikumussalam wr wb

Tabel datanya seperti ini :

Buang kolom Day dan Month, tambahkan kolom berformula untuk hitung KM

Pivotnya seperti ini :

Pasang kolom Date di area Rows -> klik kanan pivot table disebuah nilai date -> pilih group -> pilih Years dan Months saja

Di daftar field, pasang lagi field Date ke bagian Rows

Di daftar field, klik kanan field Date -> pilih add as timeline

Pasang field untuk measures ke area values, misal KM -> klik kanan yang terpasang -> field settings -> pilih Sum -> namanya diganti Total KM -> OK

Pasang lagi field KM ke values -> klik kanan yang terpasang -> field settings -> pilih Sum -> klik show values as -> ganti No Calculation jadi terpilih Running total of -> pilih field Date -> ganti nama jadi cum KM -> OK

Regards,

Kid

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to milis-belajar-e...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/727295628.298480.1689377623518%40mail.yahoo.com.

[Bayu Prasaja]

kalo supaya ada menu slicer tanggal nya bisa gk ya Pa ? soalnya yang barusan setelah saya ikuti slicernya jadi begini    

kalo boleh jadi slicernya seperti ini hehe

To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPtsUHTDeiV%2BZtHL8sZA0pSWAtAW0SK3g8rHjrD8prQk5g%40mail.gmail.com.

[Mr. Kid]

Lihat timeline Date, Months diganti dengan Days

Jadi kalau satu kesatuan tanggal supaya dapat difilter bersamaan (filter ganti tahun, maka semua berubah jadi tahun terpilih dibulan yang tampak tanggal yang tampak, dsb) maka nilai data tanggal harus sebagai 1 field utuh. Slicer untuk hal ini bernama Timeline, isinya sudah ada filter part Years, Months, Days.

Kalau field Date digroup lalu menggunakan 3 slicer, maka tidak akan terintegrasi sebagai sebuah date, tetapi jadi terpisah jadi 3 part (grup years, grup months, grup days) dan bukan sebagai 1 nilai date lagi.

Kalau sebuah nilai tanggal dibentuk di tabel data menjadi 3 kolom (kolom Tahun, Bulan, Day), maka data tanggal sejak di tabel data sudah terpisah jadi 3 part dan tidak mungkin difilter sebagai 1 kesatuan nilai tanggal lagi.

14-Jul-2023 adalah 1 kesatuan. Ketika nilai ini digrup by Years, Months, Days, maka akan tersusun hirarki vertikal :

2023               -> field Date (Years)

    Jul              -> field Date (Months)

        14-Jul     -> field Date

 --> ketiga field yang terbentuk itu sebagai object Hierarchy Date (di dalam Excel Pivot Table sana)

dan 3 elemen grup ini masih jadi satu kesatuan (artinya yang dipakai si object Hierarchy) ketika menggunakan Timeline, 

misal field Date, maka Timeline Date akan berisi 3 part yang jadi 1 kesatuan sebagai hierarchy Date yang dipasang di dalam timeline date.

Jadi, kalau Timeline Date di-filter Days bernilai 14-Jul, di Timeline Date masih ada filter Months bulan selain Jul dan ketika filter Months dipilih Aug, maka filter Days akan berubah,

karena Timeline Date berisi hiearchy [Years - Months - Days] yang membuat group [Date (Years), Date (Months), Date] masih sebagai 1 kesatuan.

Ketika menggunakan 3 slicer, maka jadinya :

2023               -> field Date (Years)      -> slicer Years

    Jul              -> field Date (Months)    -> slicer Months

        14-Jul     -> field Date                   -> slicer Days dengan TextValue berisi nilai field Date dari data source (tabel data)

--> ketiga field yang terbentuk itu sebagai object Hierarchy Date (di dalam Excel Pivot Table sana)

tetapi tidak ada slicer yang menggunakan si object hiearchy Date, maka pasti tanggal akan terpisah dan tidak akan bisa difilter sebagai 1 kesatuan.

Jadi, kalau slicer Days sudah difilter 14-Jul, maka di slicer Months sudah tidak ada lagi bulan selain Jul, karena slicer Days dan Months bukan 1 kesatuan

Semua itu logis

Jika tabel data bentuknya :

Tanggal                 Tahun           Bulan       Day

14-Jul-2023            2023            Jul             14

17-Aug-2023          2023            Aug            17

sejatinya ada 4 fields dan kolom Tahun, Bulan, Day tidak mewakili Tanggal, hanya manusia saja yang melihatnya seperti itu, padahal di dalam sana itu hal yang berbeda.

Jadi kalau slicer Day sudah di-filter 14, di slicer Bulan sudah tidak ada lagi Aug karena slicer Day dan Bulan bukan 1 kesatuan

Kesimpulannya :

1. Kalau data Date, filter sebagai 1 kesatuan adalah Timeline. Jika salah satu parts diubah oleh user, maka parts lainnya akan otomatis menyesuaikan.

2. Kalau ada 3 slicer, maka setiap klik slicer akan dilakukan filter sesuai nilai yang diklik, dua slicer lain akan disusun ulang secara terpisah berdasar data hasil filter oleh slicer pertama tadi.

3. Jika data Date akan difilter pakai slicer, maka user harus memilih slicer terkait lainnya dengan benar, jika tidak, maka data akan salah

Kalau tetap mau pakai slicer, di tabel data ditambahkan kolom Tahun, Bulan, Day

Buat 3 slicer untuk slicer Tahun, slicer Bulan, slicer Day

klik kanan setiap slicer, pilih slicer setting, atur :

1. centang Display header

2. jangan centang Hide items with no data

tekan OK -> klik (agar on) icon multiple select

Slicer Day akan menampilkan semua Day yang ada di tabel data. Jika di tabel data (dari baris 1 sampai baris terakhir) tidak ada Day bernilai 7, maka tidak akan ada pilihan Day bernilai 7

Slicer Bulan akan menampilkan semua Bulan yang ada di tabel data mengikuti urutan abjad A-Z atau Z-A. Apr akan sebagai item ke-1, Jan entah ada di-item ke berapa.

begitu juga tahun.

[Mr. Kid]

Oh iya, lupa

Dalam memanfaatkan excel pivot table, hindari pembuatan Calculated Field pada kondisi baris data tabel sumber yang sangat banyak dan atau formula calculated field yang rumit.

Calculated field itu sama saja membuat kolom baru yang berisi formula, tetapi on the fly. Jadi pivot table akan disibukkan dengan membuat semua calculated field setiap kali user mengubah konteks filter. Karena formula calculated field dikerjakan pada semua baris data, walaupun user memfilternya untuk menghasilkan 5 baris data saja.

Lebih baik tambahkan kolom berformula di tabel data dibandingkan membuat calculated field.

Contoh :

NilaiMulai     NilaiAkhir

5                      7

8                      8

Daripada membuat calculated field bernama pctCHG yang berisi formula (NilaiAkhir-NilaiAwal)/NilaiAwal

lebih baik ditabel data ditambahkan kolom bernama pctCHG berisi formula  =(NilaiAkhir-NilaiAwal)/NilaiAwal

NilaiMulai     NilaiAkhir       pctCHG

5                      7                  bunyi formulanya

8                      8

Berbeda halnya dengan Measures berupa hasil hitungan tertentu (misal di Excel Power Pivot sebagai ruang Excel Data Model)

Measures bekerja berdasar konteks (bahasa sederhananya, sesuai data yang terfilter sesuai pilihan user.

Jika user memfilter data dan hasilnya hanya ada 5 baris yang tampil di pivot table, maka measures pada dasarnya (ketika tidak ada instruksi khusus untuk mengambil data lainnya), maka hanya akan mengkalkulasi baris-baris data yang menyusun 5 baris yang ditampilkan tersebut.

On Sat, Jul 15, 2023 at 5:30 PM Bayu Prasaja <prasaja...@yahoo.com> wrote:

Siap Mr. Kid, Terima Kasih banyak ilmu baru nya, Jazakallahu khairan.
-Problem Solved-

To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPvewbowjHW6HubtBkb06OU9f8n8%2BOkWdoEQyg%2BknkC-Eg%40mail.gmail.com.

[odong nando]

hai bos kid & rekan sekalian,

saya sering banget nih buat calculate field di pivot tabel, yang pada advice mr. kid terlampir harus dihindari.

kenapa saya senang menambahkan calculate field di pivot tabel karena jika saya tambahkan di masterdatanya kalkulasi2 tersebut, pada saat penambahan baris biasanya menjadi muter2 terlalulama bahkan utk beberapa kali malah jadi keluar excel masuk lagi dengan blank sheet. saya jg tidak tau kenapa bisa begitu.

data yang saya tambahkan bisa 20k row, klo saya menambahkan di tabel master saya, biasanya membutuhkan wkt +- 5menit, dan jika lebih dari itu maka keluar sendiri dan masuk lagi dgn sheet kosong. tp semenjak saya tambahkan di calculate field pivot table nya dan saya menghilangkan kolom2 kalkulasi pada master data saya, waktu penambahan master data saya bekurang banyak dan bahkan terkadang tidak sampai 30detik.

mohon arahan dan info2 terkait yang saya alamai tersebut...

terimkasasih



odong nando

To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPs%2BBEWUHfjDvcbborMrZ0rbpXneL-z79tBSOWDHBExtCw%40mail.gmail.com.

[Mr. Kid]

Hai Nando

Formula di tabel sumber data pivot table biasanya yang sederhana saja. Lagipula jumlah kolom berformula di tabel sumber data biasanya juga tidak akan ada banyak.

Jadi, jika ada penambahan baris di tabel sumber data lalu membuat Excel crash, maka bisa jadi ada penyebab lainnya.

Coba deh di-share ke milis contoh datanya. Bagian yang rahasia diubah lebih dulu dengan nilai lain, sehingga kerahasiaan data tetap terjaga.

Bisa jadi data tersebut sudah semestinya diolah dengan Power Pivot (Excel data model), sehingga pivot table akan mendapatkan sumber data dari Power Pivot, bukan dari Excel Table atau Excel Range lagi.

Pada kondisi komputasi data dengan formula yang memang dibutuhkan tersebut cukup banyak, biasanya dibantu Power Query. Kemudian dari Power Query ke Excel Pivot Table. 

Ketika kondisi dari Power Query langsung ke Excel Pivot Table juga sudah mulai terasa lambat, maka dari Power Query ke Power Pivot untuk dintuk data modelnya (measures dan sebagainya) barulah ditampilkan dalam Excel Pivot Table.

Jadi ada cukup banyak alternatif, seperti :

0. Excel Range -> Excel Pivot Table

     untuk kasus sangat sederhana dan sudah mulai ditinggalkan, karena sesederhana apapun itu, 

     fitur Excel Table jauh lebih baik untuk dijadikan sumber data Excel Pivot Table dibanding dengan Excel Range

1. Excel Table -> Excel Pivot Table    

     jika tabel sumber data tidak perlu banyak formula yang rumit (misal vLookUp, Index Match, LookUp, SumIfs, CountIfs, dsb) 

     dan hanya butuh beberapa formula sederhana (operasi matematis sebaris [kali bagi tambah kurang mod int trunc floor ceiling round dan semacamnya])

2. Excel Table -> Power Pivot -> Excel Pivot Table

     baris data cukup banyak, tabel data bisa disusun lebih granular (ada tabel referensi [master] dan ada tabel transaksi [detil] yang berkaidah database),

     komputasi summary data yang cukup kompleks, atau melibatkan lebih dari satu tabel data sumber

3. Excel Table -> Power Query -> Excel Pivot Table

     baris data cukup banyak, komputasi antar baris dan proses extract maupun transformasi sumber data masih dibutuhkan secara intensif (misal memisah 1 kolom menjadi lebih dari 1 kolom, dsb)

4. Excel Table -> Power Query -> Power Pivot -> Excel Pivot Table

     data banyak baris, ETL masih intensif dilakukan, komputasi nilai summary juga relatif kompleks (melibatkan banyak hasil komputasi summary lain)

5. External Data -> Power Pivot -> Excel Pivot Table

     banyak tabel sumber data, ETL tidak diperlukan, banyak baris data, summary nilai bisa dari sederhana sampai yang kompleks

     cara ini juga sudah tidak digunakan sejak xl2013, walaupun Power Pivot masih bisa melakukan Get External Data secara langsung

6. External Data -> Legacy Get External Data -> Excel Pivot Table

     sudah tidak digunakan sejak xl2013, walaupun fitur Get External Data nya masih ada (biasanya hidden)

7. External Data -> Power Query -> Excel Pivot Table

     data banyak baris, banyak sumber data, ETL intensif, 

     komputasi summary relatif sederhana (cukup dengan summarize bawaan Excel Pivot Table tanpa ada interaksi antar hasil summary oleh Pivot Table)

8. External Data -> Power Query -> Power Pivot -> Excel Pivot Table

     yang begitulah....

9. External Data -> Power Query -> Excel Table -> Power Query -> Power Pivot -> Excel Pivot Table

     yang ini lebih yang begitulah...

     kompromi dengan cara ini adalah dibutuhkan 2 kali refresh data, yaitu refresh Excel Table lalu refresh Pivot Table

10. Databases (External Data) -> Power Query -> filter source by Excel Table -> Power Pivot -> Excel Pivot Table

     yang ini lebih sesuatu lagi begitulahnya...

Nomor 0, 5, 6 : sudah mulai ditinggalkan (tidak digunakan lagi)

Nomor 1 dan 2 : untuk tabel data maksimal 1jutaan baris (cukup di 1 sheet)

Nomor 3 dan 4 : sumber data bisa lebih dari 1juta baris (yang di-split dalam beberapa Excel Tables)

Nomor 7 dan 8 : sumber data bisa ada banyak table, per table bisa jutaan atau puluhan juta baris

Nomor 9 : sumber data didominasi oleh tabel dalam websites atau berupa data file (flat files [.txt, .csv, .pdf, dsb], excel files)

Nomor 10 : sumber data dari databases karena bisa ratusan juta baris, filter data source (penyusun query ke database) oleh Excel Table, dengan hasil query ke Power Pivot

Jika ada kebutuhan yang lebih kompleks lagi, barulah berpindah ke analysis service [OLAP total] (misal kalau produk ms, pakai MS SQL Server Analysis Service)

Jika sumber data sudah mencapai size (bukan jumlah baris ya) lebih dari 2GB, maka perlu penerapan filter source by Excel Table (yang ada di Nomor 10) ke nomor yang digunakan

Regards,

Kid

 

[odong nando]

baiklah bos kid...
nanti klo ketemu lagi sampe crash saya bwtkan dumminya...

utk beberapa report saya sudah menggunakan powerquery, saya tinggal menbahkan file saja dalam saut folder dan tinggal refresh pd file report saya jadi lah report update. tp saya masih bingung utk masalah hutang piutang perusahaan, karena nominal saldo pada setiap nomor invoice yang akan di reportkan bergerak, saya harus menambahkan atau mengurangi data bisa jadi saya menambah atau mengurangi s.d 20k baris pada saat mengurangi saya mendeletnya dan tidak ada masalah tp pas nanti ada penambahan itu muter2nya lama banget bahkan sampe crash.

terimakasih


odong nando

To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPsx-%2Bv6UpGfsQn_Hg%2BoN8awjEyHMefRE3Vk071%2B7XPvPA%40mail.gmail.com.