Nezávisle na typu
byznysu, hodí se vybraná měřítka analyzovat v čase. Je vcelku jedno, jestli se
jedná o příjmy, výdaje, výrobu, počty návštěv, prodané kusy... Výpočty jako
meziroční srovnání, kumulované sumy třeba i v kombinaci s porovnáním se hodí
univerzálně. Jak se s těmito výpočty vypořádat přes DAX? O tom by měl být tento
článek.
Budu vycházet z
Accessové databáze AdventureWorks Data Warehouse (jen jsem naimportoval vybrané
tabulky do Accessu a zjednodušil strukturu). Nepotřebujete tedy ani SQL Server,
abyste mohli zkoušet. Dokonce ani Access. Databáze ke stažení zde:
V modelu vytvořím
3 měřítka (Internet sales, Reseller sales a Quota). Vytvořím časovou hierarchii
s úrovněmi Rok, Kvartál, Měsíc, Datum (YQMD). Důležitou prerekvizitou je
označení časové tabulky jako kalendáře, aby nám fungovala časová logika.
Provádí se v okně PowerPivot na záložce design, mark as date table. Je
potřeba pouze vybrat sloupec s datovým typem Date. Hned na úvod musím
říct, že se v PowerPivotu a Tabulárním modelu SQL Server Analysis services
hůř pracuje s týdnem ve srovnání například s modelem multidimenzionálním.
Prerekvizity splněny,
ještě vyklikám počáteční stav kontingenční tabulky s YQMD hierarchií na
řádcích, měřítkem Reseller sales v oblasti hodnot a můžeme se pustit do
výpočtů krok po kroku.
|
Row Labels
|
Reseller sales
|
|
2005
|
$8,065,435.31
|
|
2006
|
$24,144,429.65
|
|
2007
|
$32,202,669.43
|
|
2008
|
$16,038,062.60
|
|
Grand Total
|
$80,450,596.98
|
Calculate
Ještě než se pustím
do samotných výpočtů, musím zmínit jednu z nejzásadnějších funkcí v celém
DAX a tou je funkce Calculate. Její význam je podobně stěžejní jako currentmember v MDX (o funkci
currentmember v MDX jsem blogoval zde: http://www.neoral.cz/search/label/MDX).
Calculate nám umožňuje provést výpočet ve změněném kontextu. Když se podíváte
na tabulku nahoře, zde je kontextem pro výpočet aktuální rok (např. 2005). Při
časových funkcích budeme chtít tento kontext měnit, např u meziročního srovnání
budu chtít počítat se stejným měřítkem, ale za stejné období předchozího roku.
Meziroční srovnání v procentech
Období samy mezi
sebou nemusí být porovnatelné z důvodu cyklu byznysu v jakém společnost
funguje. Například ve školícím středisku je začátek roku slabší (neschválené
rozpočty na školení), konec roku znatelně silnější (snahy utratit z rozpočtu
zbytky :)) Prosinec a Leden jsou hned vedle sebe, ale jsou neporovnatelné.
Proto je lepší porovnávat období meziročně.
Pro meziroční
srovnání budu muset prvně zjistit, jaké byly prodeje ve stejném období
předchozího roku (rok 2008 s rokem 2007, první kvartál s prvním kvartálem
předchozího roku atd.). Na stejné období předchozího roku si ukážu
nejjednodušeji přes funkci SAMEPERIODLASTYEAR, očekává jediný argument a tím je
sloupeček obsahující datum z kalendářní tabulky (prerekvizitou je označení
časové tabulky jako časové). Cílovou tabulku volím FactInternetSales, jméno
kalkulovaného člena Reseller YoY% Vzorec zatím vypadá následovně:
=SAMEPERIODLASTYEAR(DimDate[Date])
Tento DAX výraz mi
zatím vrátí pouze časový údaj se stejným obdobím předchozího roku (v MDX bych
řekl set). Tento časový údaj budu potřebovat použít jako filtr ve funkci
calculate, která mi spočítá výpočet nad změněným kontextem stejného období
předchozího roku. Výpočet
=CALCULATE([Reseller
sales],SAMEPERIODLASTYEAR(DimDate[Date]))
Data:
|
Row Labels
|
Reseller sales
|
Reseller YoY%
|
|
2005
|
$8,065,435.31
|
|
|
3
|
$3,193,633.97
|
|
|
4
|
$4,871,801.34
|
|
|
2006
|
$24,144,429.65
|
8065435.305
|
|
1
|
$4,069,186.04
|
|
|
2
|
$4,153,820.42
|
|
|
3
|
$8,880,239.44
|
3193633.969
|
|
4
|
$7,041,183.75
|
4871801.337
|
|
2007
|
$32,202,669.43
|
24144429.65
|
|
2008
|
$16,038,062.60
|
32202669.43
|
|
2009
|
16038062.6
|
|
|
Grand Total
|
$80,450,596.98
|
80450596.98
|
Stačí podělit
aktuální prodeje, versus předchozí rok a naformátovat číslo jako procento,
odečíst jedničku, aby bylo číslo vyjádřené pouze jako nárůst/pokles a ne poměr
aktuální vs předchozí.
Vzorec:
=[Reseller
sales]/CALCULATE([Reseller sales],SAMEPERIODLASTYEAR(DimDate[Date]))-1
Data:
|
Row Labels
|
Reseller sales
|
Reseller YoY%
|
|
2005
|
$8,065,435.31
|
#NUM!
|
|
1
|
-100.00
%
|
|
|
2
|
-100.00
%
|
|
|
3
|
$3,193,633.97
|
#NUM!
|
|
4
|
$4,871,801.34
|
#NUM!
|
|
2006
|
$24,144,429.65
|
199.36
%
|
|
1
|
$4,069,186.04
|
#NUM!
|
|
2
|
$4,153,820.42
|
#NUM!
|
|
3
|
$8,880,239.44
|
178.06
%
|
|
4
|
$7,041,183.75
|
44.53
%
|
|
2007
|
$32,202,669.43
|
33.38
%
|
|
2008
|
$16,038,062.60
|
-50.20
%
|
|
2009
|
-100.00
%
|
|
|
2010
|
-100.00
%
|
|
|
Grand Total
|
$80,450,596.98
|
0.00
%
|
Zbývá ošetřit dělení
prázdnou buňkou. Problém vyřešíme obdobně, jako v Excelu funkcí If. Oproti
worksheetové funkci if v DAX není možno mít ve 2 a 3 větvi funkce jiné datové
typy, nemůžu tedy použít „“ pro prázdnou buňku. Místo toho použiji formátově
neutrální funkci blank(), pro test na prázdnou buňku funkci isblank(). Pro
větší přehlednost použiji web www.daxformatter.com
pro naformátování vzorečku do čitelnější podoby
Vzorec:
=
IF (
ISBLANK (
CALCULATE (
[Reseller sales],
SAMEPERIODLASTYEAR ( DimDate[Date]
)
)
),
BLANK (),
[Reseller sales]
/ CALCULATE (
[Reseller sales],
SAMEPERIODLASTYEAR ( DimDate[Date]
)
)
- 1
)
Pro roky budoucí, které nemají zatíbm data by
vyšlo, že se jedná o 100% pokles. Chci tedy ještě testovat zda v daném období
existuje skutečnost, když ne, prázdná buňka přes. Použiji funkci OR.
Finální vzorec:
=
IF (
OR (
ISBLANK ( [Reseller sales] ),
ISBLANK (
CALCULATE (
[Reseller sales],
SAMEPERIODLASTYEAR (
DimDate[Date] )
)
)
),
BLANK (),
[Reseller sales]
/ CALCULATE (
[Reseller sales],
SAMEPERIODLASTYEAR ( DimDate[Date]
)
)
- 1
)
Data:
|
Row Labels
|
Reseller sales
|
Reseller YoY%
|
|
2005
|
$8,065,435.31
|
|
|
3
|
$3,193,633.97
|
|
|
4
|
$4,871,801.34
|
|
|
2006
|
$24,144,429.65
|
199.36
%
|
|
1
|
$4,069,186.04
|
|
|
2
|
$4,153,820.42
|
|
|
3
|
$8,880,239.44
|
178.06
%
|
|
4
|
$7,041,183.75
|
44.53
%
|
|
2007
|
$32,202,669.43
|
33.38
%
|
|
2008
|
$16,038,062.60
|
-50.20
%
|
|
Grand Total
|
$80,450,596.98
|
0.00
%
|
Kumulované sumy od začátku období až po období
aktuální
Ani
meziroční srovnání nemusí být vždy vypovídající, jeden rok je silnější březen,
duben slabší. Další rok si to prohodí. Kumulovaná suma příjmů od začátku roku
do dubna však porovnatelná je. Funkce TOTALYTD spočítá sumu od začátku roku až
do bodu v čase, kde se nacházíte, nový rok začíná znova. Ekvivaletně fungují
funkce TOTALQTD, TOTALMTD, jen na jiných úrovních.
TOTALYTD
očekává dva argumenty, měřítko se kterým chceme počítat a kde najde datum
(tabulka sloupec, nutno označit dopředu jako časovou tabulku).
Reseller
YTD by se počítalo jako vzorec:
=TOTALYTD([Reseller
sales],DimDate[Date])
Data:
|
Row Labels
|
Reseller sales
|
Reseller YTD
|
|
2005
|
$8,065,435.31
|
$8,065,435.31
|
|
3
|
$3,193,633.97
|
$3,193,633.97
|
|
4
|
$4,871,801.34
|
$8,065,435.31
|
|
2006
|
$24,144,429.65
|
$24,144,429.65
|
|
1
|
$4,069,186.04
|
$4,069,186.04
|
|
2
|
$4,153,820.42
|
$8,223,006.46
|
|
3
|
$8,880,239.44
|
$17,103,245.90
|
|
4
|
$7,041,183.75
|
$24,144,429.65
|
|
2007
|
$32,202,669.43
|
$32,202,669.43
|
|
2008
|
$16,038,062.60
|
$16,038,062.60
|
|
Grand Total
|
$80,450,596.98
|
Výsledná
tabulka například zobrazuje, jak ResellerYTD roste. První kvartál je sumou
prvního kvartálu. Druhý sumou 1+2, třetí 1+2+3
atd.
Závěr
Časových vzorců je
celá řada. V první části jsme se podívali na srovnání mezi obdobími a
kumulované sumy. Tímto ale nekončí, ještě budeme muset řešit semiaditivní
chování (co znamená semiaditivní jsem popisoval ve článku http://www.neoral.cz/2015/05/semi-aditivni-meritka-v-ssas-pomoci-mdx.html)
případně výpočty nad klouzavým obdobím. V DAX se jen složitěji pracuje s týdenní
logikou a to pravděpodobně kvůli rozdílnému začátku týdne v různých státech.
Jinak se člověk nepopsaný ani jedním analytickým modelem snáz naučí pracovat s DAX,
než třeba s MDX.
Žádné komentáře:
Okomentovat