Dönemlik pivot alma

bymarduk · 18 Mart 2024

Merhaba,
Elimde bir satış datası var
A sütunu "fatura tarihi"
B sütunu "ürün kodu"
C sütunu "ürün adı"
D sütunu "satış adedi"
E sütunu "toplam tutar" diyelim.

yapmak istediğim şey,
ürün adı, ürün kodu ve yanına sırayla;
datadaki A sütunundaki en güncel fatura tarihi (mesela düne kadar olan veriler var diyelim 17.03.2024) ve o tarihten 12 ay öncesine kadar olan verinin satış adedi ve toplam tutarını,
6 ay öncesine kadar olan verinin satış adedi ve toplam tutarını ve 3 ay öncesine kadar olan verinin satış adedi ve toplam tutarının yan yana olacak şekilde makro ile bir kerede yapabilir miyiz? bu data sürekli güncellenecek günlük ya da haftalık olarak. Son fatura tarihinden geriye giderek bu üç veriyi yan yana koyabilir miyiz?

Korhan Ayhan · 18 Mart 2024

Merhaba,

Veriniz sürekli güncellenecekse Pivot Table kullanmanız daha uygun görünüyor. Pivot Table içinde gruplandırma seçeneği bulunuyor. Onları denediniz mi?

Eğer örnek dosya paylaşırsanız ona göre çözüm önerileri gelecektir. (Dosya paylaşımı için dosya yükleme sitelerini kullanabilirsiniz..)

Haluk · 19 Mart 2024

Ben örnek bir dosya hazırladım, bakarsınız artık....

Not: Faturalardaki en son tarih değil de, kodun çalıştırıldığı tarihten itibaren geriye dönük 1 yıllık, 6 aylık ve 3 aylık veriler listelendi. Gerekirse, faturalardaki en son tarih de esas alınabilir....

Download files - Filemail

Stok Kontrol

www.filemail.com

.

bymarduk · 19 Mart 2024

Hocam merhaba,
Ellerinize sağlık öncelikle çok hızlı ve iyi çalışıyor.
Yalnız bir şey farkettim, eğer datada daha eski tarihli veri varsa, mesala 2022 tarihli diyelim, rapora o ürünleri de getiriyor ama adet ve tutar kısımları boş kalıyor, o veriler hiç gelmese olur mu?
ben kodu kurcalamaya çalıştım ama yapamadım, mesela marka sütunu da eklemek istiyorum dataya ama, kodlardan da yapmaya çalıştım ürünün karşısına yanlış markalar geldi raporda
bir de datada bölge adında bir sütunda daha olacaktı, İZMİR, ANKARA, İSTANBUL şeklinde. hem sizin ilk yaptığınız gibi total rapor olsun, onun haricinde de ayrı ayrı sheetlerde İZMİR, ANKARA ve İSTANBUL olarak 3 ayrı rapor daha çıkarabilir miyiz makro ile?

data böyle bir şey olsa: https://www.filemail.com/d/hplqobpqdfvhyuc

Haluk · 19 Mart 2024

Sorunuzun birinci kısmı için, öncekinin yerine aşağıdaki "Test2" isimli makroyu, verileri "İzmir" bölgesi için filtrelemek üzere de "Test3" isimli makroyu kullanın....

C#:

Sub Test2()
    Sheets("Rapor").Range("A2:H" & Rows.Count).ClearContents

    strSQL = " Select UK, UA, Sum(ADET12), Sum(Tutar12), Sum(ADET6), Sum(Tutar6), Sum(ADET3), Sum(Tutar3) From " & _
            " (  " & _
            "    Select [Ürün Kodu] As UK, [Ürün Adý] As UA, " & _
            "    IIF([Fatura Tarihi]>= DateAdd('yyyy',-1,Date()), [Satış Adedi], 0) AS [ADET12], " & _
            "    IIF([Fatura Tarihi]>= DateAdd('yyyy',-1,Date()), [Toplam Tutar], 0) AS [Tutar12], " & _
            "    IIF([Fatura Tarihi]>= DateAdd('m',-6,Date()), [Satış Adedi], 0) AS [ADET6], " & _
            "    IIF([Fatura Tarihi]>= DateAdd('m',-6,Date()), [Toplam Tutar], 0) AS [Tutar6], " & _
            "    IIF([Fatura Tarihi]>= DateAdd('m',-3,Date()), [Satış Adedi], 0) As [ADET3], " & _
            "    IIF([Fatura Tarihi]>= DateAdd('m',-3,Date()), [Toplam Tutar], 0) As [TUTAR3] " & _
            "    From [Data$] Where [Ürün Kodu] Is Not Null" & _
            "  ) " & _
            "  Group By UK, UA Having (Sum(ADET12) + Sum(ADET6) + Sum(ADET3))>0 "
        
    Set objConn = CreateObject("ADODB.Connection")

    strArgs = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; Readonly=False; DBQ=" & ThisWorkbook.FullName
    objConn.Open strArgs

    Set RS = objConn.Execute(strSQL)

    Sheets("Rapor").Range("A2").CopyFromRecordset RS

    objConn.Close

    Set RS = Nothing
    Set objConn = Nothing
End Sub

C#:

Sub Test3()
' IZMIR bölgesi icin verilerin alinmasi

    Sheets("Rapor").Range("A2:H" & Rows.Count).ClearContents

    strSQL = " Select UK, UA, Sum(ADET12), Sum(Tutar12), Sum(ADET6), Sum(Tutar6), Sum(ADET3), Sum(Tutar3) From " & _
            " (  " & _
            "    Select [Ürün Kodu] As UK, [Ürün Adý] As UA, " & _
            "    IIF([Fatura Tarihi]>= DateAdd('yyyy',-1,Date()), [Satış Adedi], 0) AS [ADET12], " & _
            "    IIF([Fatura Tarihi]>= DateAdd('yyyy',-1,Date()), [Toplam Tutar], 0) AS [Tutar12], " & _
            "    IIF([Fatura Tarihi]>= DateAdd('m',-6,Date()), [Satış Adedi], 0) AS [ADET6], " & _
            "    IIF([Fatura Tarihi]>= DateAdd('m',-6,Date()), [Toplam Tutar], 0) AS [Tutar6], " & _
            "    IIF([Fatura Tarihi]>= DateAdd('m',-3,Date()), [Satış Adedi], 0) As [ADET3], " & _
            "    IIF([Fatura Tarihi]>= DateAdd('m',-3,Date()), [Toplam Tutar], 0) As [TUTAR3] " & _
            "    From [Data$] Where [Ürün Kodu] Is Not Null And [Bölge]='İzmir'" & _
            "  ) " & _
            "  Group By UK, UA Having (Sum(ADET12) + Sum(ADET6) + Sum(ADET3))>0 "
           
    Set objConn = CreateObject("ADODB.Connection")

    strArgs = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; Readonly=False; DBQ=" & ThisWorkbook.FullName
    objConn.Open strArgs
   
    Set RS = objConn.Execute(strSQL)
   
    Sheets("Rapor").Range("A2").CopyFromRecordset RS
   
    objConn.Close
   
    Set RS = Nothing
    Set objConn = Nothing
End Sub

.

bymarduk · 19 Mart 2024

Hocam tekrar merhaba,
Kodlar çalışıyor tam yapmak istediğim gibi. yalnız neden olduğunu bilmiyorum ama eksik bilgi getiriyor.
çalıştığım datada rapor sayfasına getirmesi gereken veri 2100 satır iken bu makro ile 1500 satır veri getiriyor mesela, 600 adet kayıp var arada. onun dışında rapor sayfasına getirdiği veriler doğru ama

Haluk · 19 Mart 2024

Dosyayı birşey görmeden birşey söylemem mümkün değil ...

.

bymarduk · 19 Mart 2024

dosya şöyle hocam: https://www.filemail.com/d/adtlfwuhskdkdve

Haluk · 19 Mart 2024

1 yıllık sorgulamada gelmeyen verilerden örnek verirmisiniz?

.

Haluk · 19 Mart 2024

Yukarıda 5 No'lu mesajdaki kodları revize ettim, deneyip haber verirsiniz bir ara....

.

bymarduk · 20 Mart 2024

Hocam merhaba,
Yeni kodu da denedim, aşağıdaki veriler rapora gelmiyor mesela;

Ürün Kodu

Ürün Adı

Toplam Satış Adedi

Toplam Toplam Tutar

ASDASD

105-22740204E

abc

0

-178,99

#YOK

105-480200

abc

0

#YOK

240-515299

abc

0

84,39

#YOK

242-BJ61-0026-CL41

abc

1

444,34

#YOK

242-C245-0378

abc

-2

-1154,85

#YOK

242-C270-021

abc

-1

-738,65

#YOK

242-T100-312

abc

2

634,9

#YOK

500-ODULSTAND

abc

-1

-0,1

#YOK

610-604181

abc

-1

-1952,44

#YOK

610-604181-P

abc

-2

-2937,8

#YOK

610-604838

abc

0

-1135,37

#YOK

610-605255

abc

-1

-1548,12

#YOK

610-605255-P

abc

-1

-663,84

#YOK

610-607274

abc

-1

-2645,72

#YOK

620-10513T

abc

-2

-410,4

#YOK

620-604379-P

abc

0

39,16

#YOK

630-8688U

abc

0

-1110

#YOK

630-9043R

abc

0

-640

#YOK

630-9043R-P

abc

0

-68,32

#YOK

640-10178U

abc

-1

-360

#YOK

640-5341N-P

abc

0

-864

#YOK

640-605755

abc

-1

-504

#YOK

660-605072

abc

0

-543,75

#YOK

Haluk · 20 Mart 2024

5 No'lu mesajdaki "Test2" ve "Test3" isimli makrolarda aşağıdaki satırı;

C#:

            "  Group By UK, UA Having (Sum(ADET12) + Sum(ADET6) + Sum(ADET3))>0 "

aşağıdakiyle değiştirip, dener misiniz?

C#:

            "  Group By UK, UA Having Not IsNull(Sum(ADET12) + Sum(ADET6) + Sum(ADET3)) "

.

bymarduk · 20 Mart 2024

Hocam şu an verileri doğru şekilde getiriyor görünüyor. Yardımlarınız için çok teşekkür ederim

Haluk · 20 Mart 2024

Çok acele etmeyin, bir de o satırları aşağıdakiyle değiştirip deneyin .... Çünkü o haliyle, son 1 yılda satış yapılmayan verileri de getiriyordu. Aşağıdaki filtre onları listelemez..... artık hangisi sizin için daha uygunsa onu kullanırsınız.

Gerçi, son 1 yılda satış yapılmayan verileri de görmek yararlı bir bilgi...

C#:

              "  Group By UK, UA Having Sum(ADET12)<> 0 "

.

bymarduk · 20 Mart 2024

Hocam evet son yazdığınız kodla daha iyi oldu. çünkü datada örneğin 2022 yılının verisi de bulunabilir, fakat son datadanın bugün yani 20.03.2024 tarihli olduğunu varsayarsak en fazla 20.03.2023 tarihindeki veriyi almalı, o yüzden son yazdığınız kod daha iyi ve sanırım doğru çalışıyor bir iki deneme yaptım.

veyselemre · 20 Mart 2024

Bu da bölge ayrımı yapmaksızın, tüm ürünleri getirir. Ürün bazında bölgelere göre karşılaştırmada iyi olur.

Download files - Filemail

Click here to view and download these shared files from Filemail.com

www.filemail.com

Kod:

Sub test1()
    Dim strSQL$, tm
    tm = Timer
    Sheets("Rapor").Range("A2:J" & Rows.Count).ClearContents

    strSQL = "  SELECT Bölge, Marka, UK, UA, SUM(Adet365), SUM(Tutar365), SUM(Adet180), SUM(Tutar180), SUM(Adet90), SUM(Tutar90) " & _
             "  FROM " & _
             "  (  SELECT Bölge, Marka, UK, UA, " & _
             "     CDATE(FORMAT(NOW(),'dd\.mm\.yyyy')) AS Bugun, " & _
             "     FT>DATEADD('m',-6, Bugun)-1 AS T6, " & _
             "     FT>DATEADD('m',-3, Bugun)-1 AS T3, " & _
             "     SA AS Adet365, ST AS Tutar365, " & _
             "     IIF([T6], SA) AS Adet180, IIF([T6], ST) AS Tutar180, " & _
             "     IIF([T3], SA) AS Adet90, IIF([T3], ST) AS Tutar90 " & _
             "     FROM " & _
             "       ( " & _
             "          SELECT  CDATE(FORMAT([Fatura Tarihi],'dd\.mm\.yyyy')) AS FT, [Ürün Kodu] AS UK, [Ürün Adı] AS UA, Bölge, Marka,  " & _
             "          SUM([Satış Adedi]) AS SA,  SUM([Toplam Tutar]) AS ST " & _
             "          FROM [DATA$] WHERE [Fatura Tarihi]>DATEADD('yyyy',-1,FORMAT(NOW(),'dd\.mm\.yyyy') )-1 AND [Satış Adedi] IS NOT NULL " & _
             "          GROUP BY Bölge, Marka, [Ürün Kodu], [Ürün Adı], CDATE(FORMAT([Fatura Tarihi],'dd\.mm\.yyyy')) " & _
             "        ) " & _
             "   ) GROUP BY Bölge, Marka, UK, UA ORDER BY Bölge, Marka, UK, UA"

    With CreateObject("ADODB.Connection")
        .Open "Driver={MicrosoFT Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; Readonly=False; DBQ=" & ThisWorkbook.FullName
        Sheets("Rapor").Range("A2").CopyFromRecordset .Execute(strSQL)
        .Close
    End With
    Debug.Print Timer - tm
End Sub

Kod:

let
    today = Date.AddDays(Date.From(DateTime.LocalNow()),-1),
    oneYearAgo = Date.AddYears(today,-1),
    sixMonthsAgo = Date.AddMonths(today, -6),
    threeMonthsAgo = Date.AddMonths(today, -3),
    Kaynak = Excel.CurrentWorkbook(){[Name="Tablo1"]}[Content],
    #"Değiştirilen Değer" = Table.ReplaceValue(Kaynak,null,0,Replacer.ReplaceValue,{"Toplam Tutar"}),
    #"Değiştirilen Tür1" = Table.TransformColumnTypes(#"Değiştirilen Değer",{{"Fatura Tarihi", type date}, {"Ürün Kodu", type text}}),
    #"Filtrelenen Satırlar" = Table.SelectRows(#"Değiştirilen Tür1", each [Fatura Tarihi] > oneYearAgo),
    #"Gruplanan Satırlar" = Table.Group(#"Filtrelenen Satırlar", {"Bölge", "Marka", "Fatura Tarihi", "Ürün Kodu", "Ürün Adı"}, {{"Yıllık Satış Adedi", each List.Sum([Satış Adedi]), type number}, {"Yıllık Satış Tutarı", each List.Sum([Toplam Tutar]), type number}}),
    #"Özel Eklendi" = Table.AddColumn(#"Gruplanan Satırlar", "6 Aylık Satış Adedi", each if [Fatura Tarihi]>sixMonthsAgo then [Yıllık Satış Adedi] else 0),
    #"Özel Eklendi1" = Table.AddColumn(#"Özel Eklendi", "6 Aylık Satış Tutarı", each if [Fatura Tarihi]>sixMonthsAgo then [Yıllık Satış Tutarı] else 0),
    #"Özel Eklendi2" = Table.AddColumn(#"Özel Eklendi1", "3 Aylık Satış Adedi", each if [Fatura Tarihi]>threeMonthsAgo then [Yıllık Satış Adedi] else 0),
    #"Özel Eklendi3" = Table.AddColumn(#"Özel Eklendi2", "3 Aylık Satış Tutarı", each if [Fatura Tarihi]>threeMonthsAgo then [Yıllık Satış Tutarı] else 0),
    #"Değiştirilen Tür" = Table.TransformColumnTypes(#"Özel Eklendi3",{{"Yıllık Satış Tutarı", Currency.Type}, {"6 Aylık Satış Tutarı", Currency.Type}, {"3 Aylık Satış Tutarı", Currency.Type}}),
    #"Kaldırılan Sütunlar" = Table.RemoveColumns(#"Değiştirilen Tür",{"Fatura Tarihi"}),
    #"Gruplanan Satırlar1" = Table.Group(#"Kaldırılan Sütunlar", {"Bölge", "Marka", "Ürün Kodu", "Ürün Adı"}, {{"Yıllık Satış Adedi", each List.Sum([Yıllık Satış Adedi]), type number}, {"Yıllık Satış Tutarı", each List.Sum([Yıllık Satış Tutarı]), type nullable number}, {"6 Aylık Satış Adedi", each List.Sum([6 Aylık Satış Adedi]), type number}, {"6 Aylık Satış Tutarı", each List.Sum([6 Aylık Satış Tutarı]), type nullable number}, {"3 Aylık Satış Adedi", each List.Sum([3 Aylık Satış Adedi]), type number}, {"3 Aylık Satış Tutarı", each List.Sum([3 Aylık Satış Tutarı]), type nullable number}}),
    #"Sıralanan Satırlar" = Table.Sort(#"Gruplanan Satırlar1",{{"Bölge", Order.Ascending}, {"Marka", Order.Ascending}, {"Ürün Kodu", Order.Ascending}, {"Ürün Adı", Order.Ascending}})
in
    #"Sıralanan Satırlar"

bymarduk · 21 Mart 2024

@veyselemre elinize sağlık hocam sizin de teşekkürler

Dönemlik pivot alma

bymarduk

Korhan Ayhan

Administrator

Haluk

Özel Üye

Download files - Filemail

bymarduk

Haluk

Özel Üye

bymarduk

Haluk

Özel Üye

bymarduk

Haluk

Özel Üye

Haluk

Özel Üye

bymarduk

Haluk

Özel Üye

bymarduk

Haluk

Özel Üye

bymarduk

veyselemre

Özel Üye

Download files - Filemail

bymarduk