Önceki yazıda aylık, çeyrek ve yıl bazında kümülatif hesaplamaları kolaylıkla yaptık, çünkü hepsine karşılık gelen bir”time intelligence” fonksiyonu var. Fakat haftalık kümülatife doğrudan karşılık gelen bir fonksiyon yok! Bunun bir kaç sebebi var: Hafta tanımı sabit bir tanım değil, ilk hafta her yılın 1 Ocak günü mü başlıyor, yoksa ilk Pazartesi mi başlıyor, Şubat’ın 29 çektiği yıllar ne olacak gibi farklı yaklaşımların olduğu bir konu. Mesela perakendecilerin kullandığı 4-5-4 takvimi normal takvimle her zaman aynı olmayabilir.
DATESYTD veya TOTALYTD gibi fonksiyonlar olmadan da kümülatif hesaplama hesaplayabiliriz!
Aşağıdaki gibi iki metriğimiz var:
Satışlar := SUM( 'Satışlar'[Tutar] )
En Son Tarih := MAX( 'Tarih'[Tarih] )
Matrise düşürelim:
MAX fonksiyonunu daha önce de kullandık, verdiğimiz tarih sütünundaki mevcut context’e göre gördüğü maksimum -yani en son- tarihi döndürüyor. Mevcut context, 2007 Aralık satırı için, Tarih tablosundaki Aralık ayına ait son tarih, yani 31.12.2007, 2008 yılı toplam satırı için Tarih tablosunda gördüğü en son tarih, yani 31.12.2008. 2010 yılı için herhangi bir satış olmasa da 2010 yılı Tarih tablosunda var ve gördüğü en son tarih 31.12.2010.
2007 Aralık satırı için mevcut context’teki tarihler sadece 2007 Aralık ayına ait olan tarihler ve en son tarih de 31.12.2007. 2007 Aralık sonuna kadar olan kümülatif satışları bulabilmek için bu context’i şu şekilde değiştirmemiz lazım: Tarih tablosundaki ilk tarihten itibaren 31.12.2007’e kadar olan tarihler!
Formülümüz şöyle:
Satışlar Kümülatif :=
CALCULATE( [Satışlar] ;
FILTER( ALL( 'Tarih') ;
'Tarih'[Tarih] <= MAX( 'Tarih'[Tarih] )
)
)
Matrise düşürüyorum:
DATESYTD (ya da TOTALYTD) fonksiyonlarından farklı olarak, yıl değişse de kümülatif toplam almaya devam eden bir formül yazdık! Nasıl çalıştığını açalım, ‘Tarih'[Tarih] <= MAX( ‘Tarih'[Tarih] koşulunu anlamamız lazım.
FILTER, daha önce de gördüğümüz gibi bir iterator ve aynı zamanda bir tablo fonksiyonu, yani döndürdüğü şey bir tablo ve verdiğimiz tablodaki her bir satır için çalışacak ve sadece belirttiğimiz koşula uyan satırları döndürecek.
FILTER( ALL( ‘Tarih’)… diyerek Tarih tablosu üzerinde filtre oluşturan her şeyi kaldırdık ve FILTER’a üzerinde çalışacağı tablo olarak tüm tarih tablosunu gösterdik.
FILTER, Tarih tablosunda bulunan her bir satır için, yani 1.1.2007’den 31.12.2010’a kadar olan her bir satır için çalışacak ve verdiğimiz koşulu test edecek.
Yazdığımız koşulun, yani ‘Tarih'[Tarih] <= MAX( ‘Tarih'[Tarih] ‘in, solundaki ‘Tarih'[Tarih], FILTER’ın iterate ettiği Tarih tablosundaki satırın Tarih sütunu değeri. Sağdaki MAX( ‘Tarih'[Tarih] ise aynen yazdığımız metrikteki gibi, mevcut satırdaki context’te gördüğü en son tarih.
2007 Aralık satırı için FILTER’ın test ettiği koşulun karşılaştırdığı tarihler şöyle:
Tarih tablosundaki her bir satırı, her bir tarihi, 31.12.2007 ile karşılaştırıyor ve küçük eşitse FILTER’ın döndüreceği tabloya havale ediyor. Dolayısıyla 2007 Aralık satırı için FILTER’ın döndürdüğü tabloda, 1.1.2007’den 31.12.2007 ‘ye kadar olan tarihler var.
CALCULATE( [Satışlar] … ‘la da bu dönen tarihler arasındaki satışları hesaplamış oluyoruz.
Bu metriği biraz ilerletelim: Satışın olmadığı 2010 yılına ait tarihler için kümülatif göstermeye gerek yok!
Satışlar Kümülatif 2 :=
IF( COUNTROWS( 'Satışlar' ) > 0 ;
CALCULATE( [Satışlar] ;
FILTER( ALL( 'Tarih') ;
'Tarih'[Tarih] <= MAX( 'Tarih'[Tarih] )
)
)
)
Bu formüldeki koşulu -kafamızın nasıl çalıştığına bağlı olarak- farklı şekillerde yazabiliriz: [Satışlar] > 0 ise diyebiliriz veya satırdaki tarih değeri en son satış yapılan tarihten küçükse diyebiliriz. Bu ikincisinin formülünü şöyle yazabiliriz:
Satışlar Kümülatif 3 :=
IF( MIN( 'Tarih'[Tarih] ) <= CALCULATE( MAX( 'Satışlar'[Tarih] ) ; ALL( 'Satışlar' ) ) ;
CALCULATE( [Satışlar] ;
FILTER( ALL( 'Tarih') ;
'Tarih'[Tarih] <= MAX( 'Tarih'[Tarih] )
)
)
)
Önceki yazıda DATESYTD ile yazdığımız formülü de matrise düşürüyorum.
Yıllık Kümülatif = CALCULATE( [Satışlar] ; DATESYTD( 'Tarih'[Tarih] ) )
Kümülatif 2 formülü yıl değişse de kümülatif toplamaya devam ediyor. Yıl değiştiğinde sıfırdan başlamasını VALUES ile sağlayabiliriz.
Satışlar Kümülatif 4 :=
IF( COUNTROWS( 'Satışlar' ) > 0 ;
CALCULATE( [Satışlar] ;
FILTER( ALL( 'Tarih') ;
'Tarih'[Tarih] <= MAX( 'Tarih'[Tarih] )
);
VALUES( 'Tarih'[Yıl])
)
)
CALCULATE’e koşul olarak bir filtre daha vermiş olduk sadece!
Metriğe gün/hafta seviyesinde bakalım.
Hafta değiştiğinde kümülatif toplamın sıfırdan başlamasını aynen yukarıdaki gibi bir mantıkla sağlayabiliriz. Yani haftalık kümülatifleri bulabiliriz.
Satışlar Haftalık Kümülatif :=
IF( COUNTROWS( 'Satışlar' ) > 0 ;
CALCULATE( [Satışlar] ;
FILTER( ALL( 'Tarih') ;
'Tarih'[Tarih] <= MAX( 'Tarih'[Tarih] )
);
VALUES( 'Tarih'[Yıl]) ;
VALUES( 'Tarih'[Hafta No] )
)
)
Tabii bu metriği eğer matriste gün seviyesi varsa göstermek mantıklı. Modifiye edeceğimiz metriği Satışlar WTD (week-to-date) olarak isimlendireceğim.
Satışlar WTD :=
IF( COUNTROWS( 'Satışlar' ) > 0 && HASONEVALUE( 'Tarih'[Tarih] ) ;
CALCULATE( [Satışlar] ;
FILTER( ALL( 'Tarih') ; 'Tarih'[Tarih] <= MAX( 'Tarih'[Tarih] ) );
VALUES( 'Tarih'[Yıl]) ;
VALUES( 'Tarih'[Hafta No] )
)
)
Haftalık kümülatifi başka şekilde de yazmak mümkün, kurduğumuz cümleye göre formülü değiştirebiliriz: Aynı yılın aynı haftasındaki günlerin tamamını görecek şekilde context’i modifiye edelim.
Satışlar WTD-2 :=
IF( COUNTROWS( 'Satışlar' ) > 0 && HASONEVALUE( 'Tarih'[Tarih] ) ;
CALCULATE( [Satışlar] ;
FILTER( ALL( 'Tarih') ;
'Tarih'[Tarih] <= MAX( 'Tarih'[Tarih] ) &&
'Tarih'[Yıl] = SELECTEDVALUE( 'Tarih'[Yıl] ) &&
'Tarih'[Hafta No] = SELECTEDVALUE( 'Tarih'[Hafta No] )
)
)
)
Yukarıdaki kalıbın önemli bir kalıp olduğunu belirtmeliyim, sadece kümülatifler için değil başka durumlarda da çok işimize yarayacak, bu yüzden mantığını anlamakta büyük fayda var.
Yukarıdaki formülde SELECTEDVALUE yerine VALUES da kullanabilirdik. Önce Tarih tablosundaki tüm filtreleri kaldırdık, sonra koşulları && ile birleştirdik.
Aynı formülü değişken yapısıyla da yazabiliriz:
Satışlar WTD-VAR =
IF( COUNTROWS( 'Satışlar' ) > 0 && HASONEVALUE( 'Tarih'[Tarih] ) ;
VAR _SatirdakiHafta = SELECTEDVALUE( 'Tarih'[Hafta No] )
VAR _SatirdakiYil = SELECTEDVALUE( 'Tarih'[Yıl] )
VAR _ContexttekiSonTarih = MAX( 'Tarih'[Tarih] )
RETURN
CALCULATE( [Satışlar] ;
FILTER( ALL( 'Tarih') ;
'Tarih'[Tarih] <= _ContexttekiSonTarih &&
'Tarih'[Yıl] = _SatirdakiYil &&
'Tarih'[Hafta No] = _SatirdakiHafta
)
)
)
Seriyi önceki periyot karşılaştırmaları ile (önceki aya, önceki yıla, önceki haftaya göre) devam ettireceğim.
Yazıdaki modeli indirebilirsiniz.
Sadece üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!