Hoe houd je de afdelingen HR, die gewerkte uren per maand, en Financiën, die alles met ISO wil zien, tevreden? Oftewel hoe krijg ik gewerkte uren in maanden en ISO-weken in één view?
Enige tijd geleden liep ik bij het maken van een dashboard tegen het volgende probleem aan. In een dashboard probeerde ik te schakelen tussen ISO-week en maand, nu is dat niet zo ingewikkeld. Wat lastiger is vindt een laag dieper plaats: hoe zet je het aantal geplande uren per week goed om naar uren per maand?
Wat was het doel? Er moest een dashboard komen waarin te zien viel hoeveel uur er per rol/functie in een project besteed werd. Het dashboard moest laten zien wat het verschil in begrote en verbruikte uren was per rol en per projectfase. In dit blog ga ik in op de problemen, in het volgende blog leg ik uit hoe het week-dashboard wel te maken valt (zie hier). Mocht u gelijk het antwoord willen weten, dan komt hier de samenvatting: het aggregatieniveau van de data is het probleem, als het aantal besproken uren per week naar maanden omgezet wordt zijn er de volgende struikelblokken:
- Een enkele maand bevat vier weken (eigenlijk alleen februari);
- De maand en week starten niet altijd op maandag;
- Wat als een periode met besproken uren midden in een maand eindigt (zie onderstaand voor de uitgebreide uitleg).
De data
Voor dit blog wordt dummy-data gebruikt, in dit geval een fictief bouwproject. Er zijn tabellen die de basis vormen, één tabel gebaseerd op het contract met de besproken uren per rol. In de andere tabel staan de geschreven uren of urenadministratie. Stel dat de opdrachtgever een dashboard wil waarbij de gebruikers de uren per week, maand en kwartaal kunnen zien. Daarnaast wil men zien wat het verschil tussen begroot en gewerkt is.
In dit voorbeeld is er in het Data Pane van Tableau Desktop een relationship gemaakt waarbij de koppeling plaatsvindt op ‘Naam’ . (Zie deze link voor een korte uitleg over het verschil tussen join en relationship).
ISO-Week en Maand
Allereerst het ‘makkelijke probleem’: iso-weken en maanden. Zolang de gebruiker maar weet waar hij naar kijkt en hoe er geteld en vooral opgeteld wordt is er niets aan de hand. Het voornaamste verschil zit hem erin dat ISO-weken altijd 7 dagen bevatten en starten op maandag. Hierdoor kan het einde van het jaar iets anders lopen. Zo viel zondag 1 januari 2023 volgens de ISO-telling in week 52 van het jaar 2022. Als 1 januari midden in een week valt kan er zodoende een verschil en de jaar-optelling komen.
Probleem twee?
Het tweede probleem : de daadwerkelijke gewerkte uren in maanden en ISO-weken komt. Kort gezegd komt het erop neer dat het totaal aantal begrote uren op weekbasis niet één-op-één te vertalen valt naar maand of kwartaal. Alleen februari bevat buiten de schrikkeljaren . Om dit te illustreren: zie hieronder.
De som van de geplande uren in maanden en ISO-weken voor de calculator in september zou 48 uur moeten zijn. Maar SUM([Uren per week]) geeft 56 uur (8 uur teveel). Nu zou het op te lossen zijn met MIN([Uren per week]) of MAX([Uren per week]) te vermenigvuldigen met 4. Nu levert dit niet hetzelfde antwoord op, omdat het aantal geplande uren in september wijzigt. Het is dan of 16*4 of 8*4.
Allerlei oplossingen met LOD’s boden ook geen soelaas, omdat er geen duidelijk ‘anker’ is. Datum, rol en fase zijn de gehele maand gelijk, ook naam levert als som 56 uur op. Nu kan naam als veld in de view gebracht worden, maar dat zou m.b.t. privacy en overzicht niet handig zijn. Stel dat een tiental verschillende personen dezelfde rol krijgen, of dat er veel mensen betrokken zijn bij een project (bijv. +10 tekenaars)? Dit maakt een view met informatie per persoon vrij onoverzichtelijk.
Uiteindelijk was de oplossing een SQL-statement, met andere woorden, er zijn grenzen aan het ‘tweaken’ met Tableau. In dit geval schakelen tussen ISO-week en maand met verschillende aggregatieniveaus. Mocht u geïnteresseerd zijn in een wat omslachtiger oplossing, dan kunt u hier klikken.
Probleemanalyse
Hoe kwam ik achter dit probleem? Ik ben nogal fan van de mogelijkheid om in een view via een parameter te schakelen tussen week, maand en kwartaal.
Vervolgens maakt u een calculatie om te kunnen schakelen tussen [Datum] als iso-week, maand of kwartaal.
Overigens kunt u ook ‘hard’ de datumwaarden voor alle sheets die gebruik maken van één bron afdwingen. Dit kan via de taakbalk bovenin in het scherm: Data => [Naam van de bron] => Data properties … . Hier kunt u aangeven of de datumvelden Gregoriaans of ISO moeten worden.
De view kan nu opgebouwd worden, sleep [C_Select Date Part] op columns en pas de sort aan. Aangezien [C_Select Date Part] een string-value is zal de volgorde handmatig aangepast moeten worden zodat het chronologisch op tijd gaat. Dit kan door op de ‘pil’ te klikken, vervolgens op sort te klikken. In het pop-up scherm kiest u voor ‘Sort By: Field‘ , ‘Sort Order: Ascending‘, ‘Field Name: Datum‘ en ‘Aggregation: Minimum‘. Nu zal de sortering gekoppeld worden aan het veld [Datum] en van oude naar nieuwe dagen lopen.
Maar hier kwam dus het tweede probleem om de hoek kijken. Als u wilt schakelen tussen begrote uren per week en per maand of kwartaal gaat dit mis:
De zogenaamde oplossing voor gewerkte uren in maanden en ISO-weken
De “oplossing” die ik gebruikte voor het probleem van de gewerkte uren in maanden en ISO-weken was eigenlijk geen oplossing, maar een nieuw probleem. In de onderstaande afbeelding ziet u het dashboard waar ik aan werkte waarbij het gelukt leek om te kunnen schakelen tussen week en maand.
De ‘oplossing’ hield in dat ik een extra kolom ‘Periode’ had gemaakt die voor elke individuele werknemer anders was. Die kolom [Periode] kon ik weer gebruiken in allerlei LOD-statements. Met andere woorden, bij de invoer van de geplande uren per rol moest ook uitgedacht worden wie, voor hoe lang en binnen welke fase die uren golden.
Dit werd nog complexer toen één medewerker van rol veranderde binnen een project tijdens de afwezigheid van een collega. Na x weken werd die rolverandering weer terug gedraaid. Complicerende factor was dat de oude rol al die tijd actief moest blijven. (De medewerker werkte 16 uur per week in rol x en voor 6 weken deed hij rol y erbij, waardoor hij 32 uur werkte in twee rollen). Voor de opdrachtgever van het dashboard werd het er niet overzichtelijker op.
Conclusie
Kortom soms is het beter om de oplossing buiten Tableau, maar in de data zelf te zoeken. (De uiteindelijke custom SQL-code die intern gebruikt werd ging uit van een inner join op Datum >= Start AND Datum <= Eind en een left join op o.a. Rol = Rol). Mocht u wel willen weten hoe ik het opgelost heb, dan is deel 2 van dit blog hier te vinden.