V bežnej biznis analýze väčšinou potrebujete analyzovať dáta po rokoch, mesiacoch či dňoch. Čo však, ak potrebujete analyzovať dáta po minútach, resp. zobraziť za minútové intervaly v rámci nejakého časového úseku? Napr. keď chcete vizualizovať v grafe, v ktorej časti dňa prichádza najviac objednávok, či v ktorej časti dňa sa robí najviac/najmenej sledovaných úkonov? Vtedy si musíme trochu dopomôcť jazykom DAX.

Povedzme, že máme takúto tabuľku objednávok, nazvanú Objednávky:

Tabuľka je skrátená iba na prvých 12 riadkov kvôli priestoru, v skutočnosti má tisíce riadkov. Je to tabuľka objednávok, kde na každom riadku je jedna objednávka, a v stĺpci „Objednané kedy“ je presný dátum a čas objednania objednávky. A my by sme teraz chceli analyzovať tieto objednávky v rámci jedného dňa, po 10-minútových intervaloch. Čiže chceme vo výsledku vykresliť graf z týchto objednávok, kde na X-ovej osi budú časy 9:00, 9:10, 9:20 atď., a na Y-ovej osi budú počty objednávok v rámci toho intervalu. Čiže pruh zodpovedajúci času 9:00 bude obsahovať počet objednávok, vykonaných medzi 9:00:00 a 9:09:59.

Toto sa veľmi ľahko dá dosiahnuť vypočítaným stĺpcom, kde si vypočítame pre každý riadok daný časový interval. Využijeme pri tom fakt, ktorý som spomínal už v článku o dátumových operáciách v jazyku DAX – že dátumy a časy sú uložené ako desatinné čísla. Kde celá časť čísla reprezentuje dátum (počet dní od 1.1.1900), a desatinná časť reprezentuje čas – zlomkom z 24 hodín. A že s dátumami a časmi môžete v jazyku DAX pracovať ako s číslami.

Ukážeme si to najprv na jednoduchom stĺpci, kde budeme mať vypočítaný iba dátum pre každú objednávku. Tento stĺpec viete využiť neskôr na filtrovanie štatistiky len pre daný deň, napr. pomocou slicera.

Vytvoríme teda nový vypočítaný stĺpec, ktorý nazveme „Iba dátum“, a ktorého vzorec bude nasledovný:

Iba dátum = INT(‚Objednávky'[Objednané kedy])

Funkcia INT zo zadaného čísla vráti iba jeho celočíselnú časť – čiže oreže z neho všetko za desatinnou čiarkou. A keďže aj dátumy s časmi sú v skutočnosti uložené ako čísla, tak to môžeme použiť aj tu. Výsledný stĺpec bude vyzerať takto:

Tam vidíte, že vzorec zbehol, ale výsledok zobrazuje ako čísla. To upravíme ľahko tak, že v hlavnom menu zmeníme typ údajov v stĺpci na Dátum:

…a výsledok už teraz bude vyzerať správne:

Nemohli sme to však spraviť rovno tak, že by sme spravili iba kópiu toho pôvodného stĺpca a zmenili mu typ na Dátum? Mohli, ale PowerPivot nepozná dátový typ „Dátum“,  iba „Dátum a čas“, a ten výber akože typu „Dátum“ vám zmení typ stĺpca na „Dátum a čas“ a skryje časovú zložku. Čiže ten nenulový čas tam bude stále, len sa nebude zobrazovať. A vy sa budete diviť, kde je chyba… Preto je potrebné to spraviť takto (alebo to urezať ešte v Power Query).

Keď už vidíte, že to takto funguje, tak môžeme konečne pristúpiť k výpočtu stĺpca s časovým intervalom pre každú objednávku. Čiže stĺpca, kde si vypočítame príslušné minútové intervaly pre každý stĺpec. Vyrobte si teda ďalší vypočítaný stĺpec, s názvom „Interval“, a s takýmto vzorcom:

Interval =
VAR minuty = 10
VAR dlzkaDnaVSekundach = 24*60*60
RETURN
INT(‚Objednávky'[Objednané kedy]) + ((FLOOR(((‚Objednávky'[Objednané kedy] – INT(‚Objednávky'[Objednané kedy])) * dlzkaDnaVSekundach) / (minuty*60); 1) * minuty*60) / dlzkaDnaVSekundach)

…alebo vypeknené cez DAX Formatter:

Prípadne pre tých, ktorí majú starší Excel ako 2016, alebo v staršom SSAS Tabulare, je to takto:

= INT(‚Objednávky'[Objednané kedy]) + ((FLOOR(((‚Objednávky'[Objednané kedy] – INT(‚Objednávky'[Objednané kedy])) * 24*60*60) / (10*60); 1) * 10*60) / (24*60*60))

Tento vzorec vytvorí nový vypočítaný stĺpec, ktorý zoberie pôvodný dátum s časom v stĺpci „Objednané kedy“, a znormalizuje ho na X-minútový interval. Pričom dĺžka intervalu v minútach je v daxovej premennej „minuty“ (resp. v tom druhom vzorci všade, kde vidíte číslo 10). Čiže napr. z dátumu/času „01.08.2018 09:12:57“ vytvorí dátum/čas „01.08.2018 09:10:00“. Využívame tam základné matematické prepočty – v prvej časti vzorca zoberieme čistý dátum (funkcia INT), a k nemu pripočítame normalizovaný čas. Ten si vypočítame najprv tak, že si zistíme časovú zložku (hodnota v stĺpci „Objednané kedy“ mínus tá istá hodnota bez času), prenásobíme ju počtom sekúnd v 1 dni,  predelíme počtom sekúnd v cieľovom intervale, a funkciou FLOOR to zaokrúhlime ma celé nižšie číslo (áno, išla by použiť aj skratkovitá funkcia INT). To potom prenásobíme počtom sekúnd v intervale (čím získame počet sekúnd pre začiatok daného intervalu od začiatku daného dňa), a to predelíme počtom sekúnd v dni, aby sme z toho dostali znova desatinné číslo, ktorým sa interne reprezentuje čas v rámci dňa. Čiže celý vzorec robí to, že zoberie čistý dátum zo zadaného stĺpca, a pripočíta k nemu normalizovanú hodnotu zlomku pre čas v rámci toho dňa. Čím vyrobí príslušné minútové intervaly pre každý dátum/čas v tejto tabuľke. Ak je to ťažké na pochopenie, tak si to prejdite zopárkrát, je to matematika na úrovni základnej školy 😉

Keď ale takýto stĺpec zobrazíme, tak sa zobrazí ako desatinné číslo:

To je preto, lebo sme s tým pracovali ako s číslom, a výsledok je teda tiež desatinné číslo. Preto to podobne ako v predchádzajúcom prípade opravíme tak, že zmeníme typ stĺpca na „Dátum a čas“, a výsledok už bude zobrazený správne:

Tam už vidíte, že hodnoty z pôvodného stĺpca sú teraz normalizované na 10-minútové intervaly, čo je presne to, čo sme chceli.

Teraz si buď spravte kópiu tohto stĺpca a zmeňte mu typ na „Čas“, alebo iba tomu pôvodnému stĺpcu zmeňte typ na „Čas“. A potom nastavte formát tomu stĺpcu na „HH:mm“, aby to zobrazovalo v tom stĺpci čas iba s hodinami a minútami:

Teraz už iba stačí spraviť graf, kde:

1. na X-ovej osi bude ten posledný stĺpec, ktorý sme práve vytvorili,
2. na Y-ovej osi bude počet objednávok (to spravíte napr. pridaním stĺpca ID a nastavením jeho súhrnnej funkcie na Počet).

Výsledok, napr. v Power BI, po naformátovaní grafu, a odfiltrovaní len na 1 deň pomocou slicera zo stĺpca „Iba dátum“, bude vyzerať takto:

Keď teraz chcete zmeniť interval napr. na 15 minútové intervaly, tak vo vzorci zmeňte premennú „minuty“ z hodnoty 10 na hodnotu 15 (resp. v tom staršom vzorci všetky výskyty čísla 10 na 15), a všetko sa pekne prepočíta na nové intervaly:

Takto jednoduché je teda zobrazovať dáta aj v časových intervaloch. Celý trik bol iba v tom, že sme si znormalizovali hodnoty z pôvodného stĺpca do nového vypočítaného stĺpca, a podľa neho sme dali vykresliť graf za minútové intervaly. Čiže to, čo sa bežne robí s vypočítanými stĺpcami, keď chcete mať pruhy/výseky v grafe vyrobené podľa svojich pravidiel. Môže byť ešte niečo jednoduchšie? 🙂