In deze blog vertel ik je alles wat ik weet over de Multi-Row Formula Tool in Alteryx. Op welke vlakken heeft deze tool dezelfde functionaliteit als de normale formula tool en op welke vlakken verschilt hij? Wat maakt de tool zo krachtig? Lees door en je komt erachter!
Multi-row formula vs normale formula
De multi-row tool kan je beschouwen als de grote broer van de normale formula tool. Via beiden kun je nieuwe kolommen maken of bestaande kolommen aanpassen, via zelfgeschreven formules. De gewone formula tool heeft hier wel een streepje voor op de multi-row tool. Hij helpt je namelijk bij het schrijven doordat de expression editor IntelliSense bevat. Deze vult bijvoorbeeld de functies aan wanneer je begint met typen en kleurt formules (oranje) en kolommen (paars). Daarnaast krijg je ook een melding als de formule een fout bevat. Mocht je een ingewikkelde formule moeten gebruiken dan kun je er altijd voor kiezen deze in een formula tool te schrijven, daar op te slaan en vervolgens de opgeslagen formule te openen in de multi-row formula. Hieronder zie je hoe je dat doet.
Expression editor in de formule tool. Druk op de ‘floppy disk’ om de expressie op te slaan.
Kies via tab ‘saved expressions’ de juiste opgeslagen formule. Via de preview kan je checken of je de juiste hebt.
Ondanks dat het schrijven van formules dus iets ingewikkelder is, heeft deze tool veel extra’s te bieden. De formula tool kan namelijk alleen kolommen bereiken in dezelfde rij. De multi-row formula kan, je zou het niet zeggen met zo’n naam, meerdere rijen gebruiken bij het berekenen van een resultaat. Hoe je dit doet leg ik je uit aan de hand van de configuratie van de tool.
Configuratie
Het configuratiescherm van de tool kun je zien in onderstaande afbeelding.
Voordat je begint met je formule dien je eerst te bepalen of je een bestaande kolom wilt aanpassen, of een nieuwe kolom wilt maken. Wil je van data type veranderen? Dan kan je niet anders dan een nieuwe kolom aanmaken.
Vervolgens kies je het aantal rijen wat je omhoog of omlaag wilt kunnen gebruiken voor de expressie die je wilt schrijven. Standaard is dit 1, maar er zijn scenario’s mogelijk waarbij je verder terug of vooruit zou willen ‘kijken’. Goed om te weten is dat de multi-row formule voor iedere rij apart het resultaat berekent van de formule die je geschreven hebt, waarbij van boven naar beneden gewerkt wordt. Voor de eerste berekening is rij 1 dus de actieve rij. Als deze berekent is wordt voor rij 2 het resultaat berekent en is rij 2 dus ook de actieve rij. Van rij 2 naar rij 3, enzovoorts totdat de laatste rij berekent is.
Een belangrijke optie is om te bepalen welke waarde rijen die niet bestaan hebben. Dit klinkt wellicht gek. Stel je verwijst in je formule naar een waarde in de vorige rij. Als de actieve rij de eerste rij is, dan is er geen voorgaande rij. Voor zo’n geval is het dus belangrijk dat je bepaald wat de waarde wordt zodat de berekening wel uitgevoerd kan worden. Je kunt kiezen uit null, 0 / leeg of de waarde van de dichtstbijzijnde rij die wél een geldige waarde bevat.
Je kunt er daarnaast voor kiezen om te groeperen op één of meerdere kolommen. Dit verandert de manier van berekenen. Aan de hand van een voorbeeld wordt dit later uitgelegd.
Vervolgens vind je een blok met drie tabbladen: ‘variables’, ‘functions’ en ‘saved expressions’. Meeste uitleg behoeft tabblad variables, welke uitgelegd wordt aan de hand van onderstaande tabel.
Waar je normaal in een formule alleen naar de kolom verwijst moet je nu dus ook vermelden welke rij van die kolom je wilt gebruiken. De actieve rij is altijd beschikbaar om te kiezen. Het aantal rijen wat je omhoog of omlaag kan gebruiken ten opzichte van de actieve rij is afhankelijk van de instelling bij ‘Num rows’. De formule [Row-1:Sales] + [Sales] zal dus de waarde van kolom ‘Sales’ uit de voorgaande rij ten opzichte van de actieve rij optellen bij de waarde van de kolom ‘Sales’ in de actieve rij.
Via de tabbladen ‘functions’ en ‘saved expressions’ kun je functies dan wel opgeslagen functies toevoegen aan de formule die je schrijft.
Het laatste blok is de expression editor, hier vul je de formule in die je wilt gebruiken om tot het gewenste resultaat te komen.
Voorbeelden
Ik vind het altijd het makkelijkst om een tool te leren te begrijpen wanneer ik deze aan het werk zie.
Hieronder vind je drie voorbeelden waarbij de multi-row formula gebruikt kan worden:
- Een cumulatieve waarde berekenen
- Uitvullen van null waarden
- Een gemiddelde berekenen (zonder de dataset te verkleinen zoals met een summarize tool gebeurd)
Cumulatieve waarden berekenen
In onderstaande tabel vind je omzet (sales) per regio (market) per maand (month).
Om de cumulatieve waarden van de omzet te berekenen gebruik je een simpele expressie:
waarde huidige regel = totaal van de vorige regel + sales huidige regel
In de expression editor zal dit er zo uitzien (waarbij cumulative sales de nieuwe kolom is die wordt gemaakt door de multi-row formula):
Dit resulteert in onderstaande tabel:
De waarde van regel één van de nieuw kolom ‘Cumulative sales’ is de waarde van kolom ‘Cumulative sales’ uit de voorgaande rij ([Row-1:Cumulative sales], die overigens niet bestaat en waarde 0 heeft) + de waarde van kolom ‘Sales’ [Sales] uit de actieve rij. Dit is dus 0 + 200 = 200.
De waarde van regel twee van de nieuwe kolom is de waarde van kolom ‘Cumulative sales’ uit de voorgaande rij (200) + de waarde uit kolom ‘Sales’ uit de huidige rij. Dit is dus 200 + 300 = 500.
Het zou natuurlijk veel logischer zijn in dit voorbeeld om de cumulatieve omzet per regio te zien. Hiervoor kunnen we de ‘group by’ functionaliteit gebruiken. Als we groeperen op regio zal de multi-row formula iedere regel waarbij de regio ongelijk is aan de regio uit de voorgaande rij als eerste regel beschouwen. Het is dan dus voor de formule niet mogelijk om terug te kijken. Voorgaande rijen krijgen dan in de formule de waarde die je gekozen hebt voor niet bestaande rijen (null, 0 of dichtstbijzijnde geldige waarde).
Omdat ik gegroepeerd heb op de kolom ‘Market’ wordt regel vier, waar regio B voor het eerst voorkomt, als nieuwe regel beschouwd. Om deze reden is het resultaat van [Row-1:Cumulative sales] weer 0. Het resultaat is dus 0 + 150 = 150.
'Uitvullen' van null waarden
Soms moet je met data werken die bijvoorbeeld is vormgegeven voor of door gebruik in Excel. Hieronder zie je zo’n voorbeeld. De kolom ‘market’ (regio) zou je willen uitvullen naar beneden, zodat in iedere regel een regio ingevuld is (‘A’ in regel 1, 2 en 3, ‘B’ in regel 4, 5 en 6 etc.).
In bovenstaande tabel wordt iedere regio nu één keer weergegeven gevolgd door null waarden totdat de volgende regio begint. Je weet inmiddels dat de multi-row formule van boven naar beneden wordt uitgevoerd. Wanneer er een waarde ingevuld is (‘A’ in rij 1) wil je deze behouden. Wanneer de waarde echter null is dan wil je de waarde uit de rij daarboven overnemen (null uit rij 2 wordt dan overschreven door de waarde uit rij 1, waar ‘A’ staat). Dit resulteert in onderstaande expressie:
De expressie volgt een iets andere volgorde, maar er staat hetzelfde. Als de huidige cel null is, neem dan de waarde van de rij erboven over. Wanneer de waarde ongelijk is aan null dan laat je die waarde staan.
Een gemiddelde berekenen
Als laatste voorbeeld ga ik voor onderstaande tabel de gemiddelde omzet berekenen per regio, waarbij dit gemiddelde aan iedere rij voor de desbetreffende regio moet worden toegevoegd.
In dit geval wil ik twee rijen achteruit/vooruit kunnen kijken ten opzicht van de actieve rij. De waarde voor niet bestaande rijen stel ik in op 0 / leeg. De logica die nodig is om dit te berekenen zal er als uitzien:
Indien de waarde van ‘Month’ in de actieve rij gelijk is aan ‘Jan’ neem het gemiddelde van [Sales] + [Row+1:Sales] + [Row+2:Sales]
Indien de waarde van ‘Month’ in de actieve rij gelijk is aan ‘Feb’ neem het gemiddelde van [Row-1:Sales] + [Sales] + [Row+1:Sales]
Indien de waarde van ‘Month’ in de actieve rij gelijk is aan ‘Mar’ neem het gemiddelde van [Row-2:Sales] + [Row-1:Sales] + Sales]
De expression editor en het resultaat zie je hieronder:
Tot slot
Ik hoop dat je aan de hand van de voorbeelden de multi-row formula tool beter bent gaan begrijpen. Wanneer je hem begint te gebruiken weet ik zeker dat je hem net zo zal gaan waarderen als ik! Deze blog is onderdeel van een serie die ik ben gaan schrijven, over tools die ik super fijn vind om te gebruiken.
Als je andere blogs wilt lezen over software als Tableau en Alteryx, een training wilt volgen of een consultant wilt boeken kijk dan op The Information Lab NL voor meer informatie.