Aggr v Qliku: vgnezdene agregacije s primeri

Večina mer v Qliku je ena agregacija: Sum(Prodaja), Count(DISTINCT Kupec). Nekatera vprašanja pa zahtevajo dva koraka. Kolikšna je povprečna prodaja na kupca? Najprej moramo sešteti prodajo za vsakega kupca posebej, šele nato izračunati povprečje teh vsot. Za to je funkcija Aggr.

Kaj naredi Aggr

Aggr() izračuna izraz za vsako vrednost ene ali več dimenzij in vrne navidezno tabelo rezultatov. Nad to tabelo nato izračunate zunanjo agregacijo:

Avg( Aggr( Sum(Prodaja), Kupec ) )

Beremo od znotraj navzven:

1. Aggr(Sum(Prodaja), Kupec) — za vsakega kupca izračunaj vsoto prodaje (navidezna tabela: kupec → vsota),
2. Avg(…) — izračunaj povprečje teh vsot.

Če poznate SQL: to je podpoizvedba z GROUP BY, nad katero izračunate še eno agregacijo.

Osnovni primeri

Najboljši mesec

// Prodaja v najboljšem mesecu
Max( Aggr( Sum(Prodaja), LetoMesec ) )

// Kateri mesec je to bil
FirstSortedValue( LetoMesec, -Aggr( Sum(Prodaja), LetoMesec ) )

FirstSortedValue vrne vrednost polja, razvrščeno po drugem argumentu; minus pomeni padajoče, torej največjo vrednost.

Število kupcev nad pragom

// Koliko kupcev ima več kot 10.000 € prodaje
Sum( Aggr( If(Sum(Prodaja) > 10000, 1, 0), Kupec ) )

Isto lahko zapišete s set analysis: Count({<Kupec = {"=Sum(Prodaja) > 10000"}>} DISTINCT Kupec). Set analysis je običajno hitrejši; Aggr je prožnejši, kadar pogoj ni preprost.

Povprečje po dveh dimenzijah

// Povprečna mesečna prodaja na kupca
Avg( Aggr( Sum(Prodaja), Kupec, LetoMesec ) )

Aggr kot izračunana dimenzija

Aggr lahko uporabite tudi kot dimenzijo v grafu. Tako ustvarite skupine, ki jih v podatkih ni.

ABC segmentacija kupcev

=Aggr(
    If(Sum(Prodaja) >= 50000, 'A',
      If(Sum(Prodaja) >= 10000, 'B', 'C')),
    Kupec)

S to dimenzijo in mero Count(DISTINCT Kupec) dobite graf s številom kupcev v vsakem razredu. Ker se izračuna sproti, se razredi prilagodijo izborom — npr. ABC za izbrano leto ali regijo.

Top 10 kupcev

=Aggr( If(Rank(Sum(Prodaja)) <= 10, Kupec), Kupec )

Kupci zunaj prvih deset dobijo prazno vrednost; v nastavitvah dimenzije izklopite prikaz praznih vrednosti. Za preprost top 10 pa je pogosto dovolj kar omejitev dimenzije v lastnostih grafa (Limitation → Fixed number → Top 10) — brez Aggr.

Aggr in set analysis

Aggr sam ne sprejme set izraza. Set izraz zapišite v notranjo agregacijo:

// Povprečna prodaja na kupca v zadnjem izbranem letu
Avg( {<Leto = {$(=Max(Leto))}>}
     Aggr( Sum({<Leto = {$(=Max(Leto))}>} Prodaja), Kupec ) )

Notranji set določa, katera prodaja se sešteje. Zunanji set (pred Aggr) omeji, kateri kupci sploh nastopajo v navidezni tabeli — brez njega bi se v povprečje šteli tudi kupci z ničelno prodajo v tem letu, če jih trenutni izbori vključujejo. Pravilo: kadar uporabljate set analysis v Aggr, ga ponovite v obeh agregacijah.

Pred Aggr preverite TOTAL

Pogosta potreba: delež vrstice v skupini, npr. delež izdelka v prodaji kupca v tabeli s stolpcema Kupec in Izdelek. To ne potrebuje Aggr, ampak kvalifikator TOTAL:

// Delež izdelka v prodaji kupca
Sum(Prodaja) / Sum( TOTAL <Kupec> Prodaja )

TOTAL <Kupec> pomeni »prezri vse dimenzije grafa razen Kupec«. Je preprostejši in hitrejši od Aggr.

Če pa vrednost iz grobe ravni res potrebujete v vsaki vrstici drobnejše tabele in jo želite naprej uporabiti v pogoju, pride v poštev NODISTINCT:

// V vsaki vrstici (Kupec × Izdelek) prikaži skupno prodajo kupca
Aggr( NODISTINCT Sum(Prodaja), Kupec )

Brez NODISTINCT bi se vrednost prikazala le v eni vrstici vsakega kupca, ostale bi bile prazne.

Kumulativa znotraj Aggr

Pri funkcijah, ki so odvisne od vrstnega reda (Above, RangeSum), lahko dimenziji v Aggr določite razvrščanje:

// Kumulativna prodaja po mesecih, za vsakega kupca posebej
Aggr(
    RangeSum( Above( Sum(Prodaja), 0, RowNo() ) ),
    Kupec,
    (LetoMesec, (NUMERIC, ASCENDING))
)

Brez navedenega razvrščanja bi se meseci v navidezni tabeli lahko uredili po vrstnem redu nalaganja in kumulativa bi bila napačna.

Pogoste napake

• Aggr brez zunanje agregacije. V tabeli po regijah izraz Aggr(Sum(Prodaja), Kupec) vrne prazno vrednost, ker ima regija več kupcev — Qlik ne ve, katero vrednost prikazati. Vedno ga ovijte: Avg(…), Sum(…), Max(…).
• Dimenzije grafa manjkajo v Aggr. V tabeli po regijah, kjer želite povprečje na kupca za vsako regijo, naj Aggr vsebuje obe: Avg(Aggr(Sum(Prodaja), Regija, Kupec)).
• Počasnost. Aggr izračuna navidezno tabelo ob vsakem kliku. Če segment ni odvisen od izborov (npr. ABC za vso zgodovino), ga izračunajte v skripti in shranite kot navadno polje.
• Aggr, kjer zadošča TOTAL ali set analysis. Preprostejši izraz je hitrejši in ga bo naslednji razvijalec razumel.

Povzetek