De offset-functie gebruiken in uitgebreide datumtabellen

Ik ga je laten zien hoe je de offset-functie in LuckyTemplates gebruikt . Het is een van de meest dynamische tools die u kunt gebruiken, vooral in uw datumtabellen.

Als je eenmaal onder de knie hebt hoe je het kunt toepassen in je time intelligence-projecten, kun je meer diepgaande inzichten krijgen die je met geen enkele andere aanpak zult krijgen. Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

Inhoudsopgave

• De mechanica van de offsetfunctie begrijpen
• Voorbeeld van een weekoffsetkolom
• Offsets gebruiken in DAX
• Offsets voor specifieke weken
• Conclusie

De mechanica van de offsetfunctie begrijpen

Ik zal beginnen met u de mechanica van de offset-functie te laten zien.

Offsets tonen het verschil tussen de datum van vandaag en de andere datums in uw gegevens.

Zoals je kunt zien in mijn datumtabel hieronder, heb ik me gericht op kwartaal en jaar , zodat ik de daadwerkelijke gegevensbeweging kan zien.

De paarse rij geeft de huidige periode weer . Merk op dat de huidige periode altijd een offsetwaarde van 0 heeft .

Daarboven, in het geel, worden alle gegevens uit het verleden weergegeven . U zult zien dat de cijfers hier 0 of kleiner dan 0 zijn, vertegenwoordigd door de negatieve gegevens .

Dit betekent dat voor gegevens uit de toekomst deze positieve waarden zullen laten zien . Afhankelijk van de granulariteit van de kolom kan deze ook een 0 bevatten .

Dus wat maakt offsets krachtig?

Offsets kunnen opeenvolgende getallen zijn die het verschil tussen verschillende gegevens in de tabel vertegenwoordigen . Maar wat offsets geweldig maakt, is dat ze geen grenzen hebben .

Kwartalen lopen bijvoorbeeld van 1 tot 4, wat staat voor de 4 kwartalen in een jaar. Maanden lopen van 1 tot 12, terwijl weken kunnen lopen van 1 tot 52 of 53, afhankelijk van welk jaar het is. Maar als ik eenmaal bij het laatste cijfer ben, moet ik opnieuw beginnen.

Dit is niet het geval voor compensaties. Als het gaat om offsets, tonen ze altijd een relatieve positie ten opzichte van de huidige datum en de andere datum waarnaar wordt verwezen .

Dus elke keer dat de datumtabel wordt vernieuwd, wordt ook de logica die ervoor zorgt dat de offsets werken, bijgewerkt. Dit betekent dat er ook een bijgewerkte waarde in de datumtabel wordt weergegeven.

Voorbeeld van een weekoffsetkolom

Om u een duidelijker beeld te geven van hoe de offset-functie werkt, zal ik u een voorbeeld geven van hoe deze wordt toegepast.

In mijn voorbeeldgegevens wordt de datum van vandaag weergegeven als 24 april 2020 .

Nogmaals, de offset-principes stellen dat de huidige periode wordt weergegeven door 0, toekomstige gegevens worden weergegeven door positieve waarden en perioden uit het verleden worden weergegeven door negatieve getallen .

Ik heb ook een slicer op mijn pagina die de WeekOffset-waarde bevat. Momenteel toont het alle waarden van -53 tot 0, waarbij 0 de huidige week vertegenwoordigt .

24 april (dat is vandaag) valt in week 17 .

Deze uitgebreide datumtabel bevat alleen de ISO-weeknummers . Dit betekent dat weken beginnen op een maandag . Dit betekent ook dat een week altijd 7 dagen zal bevatten .

Maar er zijn veel aangepaste weekvereisten die niet dezelfde logica volgen. U kunt meer over deze gevallen te weten komen in verschillende onderwerpen die op het LuckyTemplates-forum worden behandeld.

U kunt ook overwegen om Maand & Jaar te gebruiken in plaats van Week & Jaar als dat beter van toepassing is op uw situatie.

Als u teruggaat naar de voorbeeldgegevens, is het u misschien opgevallen dat de waarden van WeekOffset zich in een perfect volgnummerbereik bevinden .

Dit komt doordat ze overal dezelfde granulariteit hebben . Er zijn geen dubbele waarden en geen ontbrekende waarden.

Natuurlijk wordt de hier gebruikte Totale Verkoopmaatstaf op de gebruikelijke manier uitgevoerd, met slechts een normale over Verkoop voor de Hoeveelheid maal de Prijs . Vervolgens wordt alles samengevoegd om een ​​wekelijkse waarde te krijgen.

Maar wat als ik alleen de laatste 4 weken uitverkoop wil zien?

Ik hoef alleen mijn slicer te veranderen in een bereik van -3 tot 0 .

Nu laat mijn tabel alleen de verkoop van de afgelopen vier weken zien.

Dus naarmate ik door de tijd reis, wordt mijn datumtabel bijgewerkt. Hetzelfde gebeurt als nieuwe verkoopresultaten in de feitentabel worden geladen.

Offsets gebruiken in DAX

Deze keer laat ik zien hoe de offset-functie kan worden gebruikt in DAX-maten.

Ik heb een tabel met de Week & Jaar en Totale Verkoop .

Dus als ik de verkopen van de vorige week wil berekenen zonder compensaties, moet ik eerst het CurrWeek- nummer extraheren en vervolgens controleren wat de CurrYear- waarde is.

Als ik een jaargrens moet overslaan, moet ik uitrekenen wat het Weeknummer is van dat laatste jaar.

Van daaruit zou ik SUMX gebruiken als een herhalende functie op de datumtabel. Ik zou ook moeten kijken of de CurrWeek week nummer 1 is .

Als het geen week 1 is, hoef ik alleen maar 1 af te trekken van de CurrWeek-waarde voor CurrYear . Daarna kan de Totale Verkoop eindelijk worden opgeteld .

Als u naar de onderstaande tabel kijkt, kunt u nu zien dat alle waarden perfect zijn geaggregeerd. De waarden die verschijnen onder Total Sales worden gewoon overgedragen naar de volgende week.

Zelfs als ik van 2017 naar 2018 een jaargrens overschrijdt, doorbreekt het patroon niet. De Total Sales van de laatste week van 2017 is nog perfect berekend voor week nummer 1 van 2018.

Deze keer ga ik dezelfde berekening doen, maar met datumtabel-offsets toegepast.

Zo ziet die maat eruit als ik offsets gebruik.

Dus om de PrevWeek- waarde te krijgen, hoef ik alleen maar naar de geselecteerde WeekOffset te verwijzen en 1 af te trekken.

Vervolgens gebruik ik de functie voor de totale verkoop over datums waarbij de WeekOffset gelijk is aan de PrevWeek- waarde.

Kijkend naar de resultaten, lijken de resultaten perfect te zijn, zelfs in de overgang van 2017 naar 2018.

Het probleem zit hem echter in de Total . Dit is duidelijk niet de juiste waarde.

Hier is hoe ik dat ga oplossen.

Merk allereerst op dat de kolom Week & Jaar in oplopende volgorde staat .

Ik ga dat veranderen en in plaats daarvan de kolom in aflopende volgorde sorteren , met 2020 bovenaan.

Omdat er geen context uit Week & Jaar komt , identificeert het niet de offsetwaarde van de huidige week, maar wordt er nog steeds 1 van afgetrokken . Maar wat ik wel kan doen, is een cumulatief totaal toepassen om het totaal aan te passen .

Zo ziet de maatregel eruit:

Het begin van de maat verandert niet. Het is precies hetzelfde als de vorige maatregel die ik toepaste.

Maar aangezien ik een patroon moet gebruiken dat lijkt op cumulatieve totalen, moet ik de CALCULATE- functie gebruiken op de FirstWeek over de datums met 1 ervan afgetrokken.

Ik moet ook de MAX WeekOffset- waarde van de ALLSELECTED- datums identificeren en er ook een van aftrekken.

Vervolgens gebruik ik de CALCULATE- functie opnieuw voor de Total Sales . Ik gebruik de functie om ALLE datums op te nemen . De waarde van WeekOffset moet groter zijn dan of gelijk zijn aan FirstWeek en kleiner dan of gelijk zijn aan LastWeek .

Nogmaals, dit volgt het basispatroon voor cumulatieve totalen.

Dan kan ik doorgaan met het controleren van het eindtotaal met behulp van de patronen.

Dus INDIEN de Week & het Jaar INSCOOP IS , verwacht ik dat de resultaten de LWSales laten zien . Maar als dat niet het geval is, verwacht ik dat de LWTotal verschijnt.

Dus als ik terugga naar mijn tafel, wordt nu het juiste totaal weergegeven.

Offsets voor specifieke weken

Laat me teruggaan naar het voorbeeld dat ik eerder gebruikte, waarbij ik de gegevens filterde om alleen de laatste vier weken weer te geven.

Als ik inga op de maat, kan ik met offsets tot dezelfde resultaten komen. Dit is hoe dat eruit zou zien.

Ik moest alleen de huidige WeekOffset- waarde identificeren voor de context die uit de rijen komt.

Als ik dat eenmaal heb, kan ik de CALCULATE- functie toepassen op de totale verkoop van ALLE datums waarop de WeekOffset groter is dan of gelijk is aan ThisWeek min 3, of kleiner dan of gelijk aan de waarde voor ThisWeek .

Dit retourneert precies dezelfde waarde als het voorbeeld dat ik probeer te repliceren.

Cumulatieve totalen op basis van maandelijkse gemiddelde resultaten in LuckyTemplates
Gegevensnormalisatie in LuckyTemplates voor verschillende dagen Resultaten
Realiseer snel een uitgebreide datumtabel in LuckyTemplates

Conclusie

De voorbeelden die ik u hierboven heb laten zien, bewijzen hoe dynamische offsets werkelijk zijn. U kunt ze gebruiken in filters, segmenten en zelfs in DAX-berekeningen.

Offsets kunnen ook worden gebruikt in uitgebreide datumtabellen voor weken, maanden, kwartalen, jaren en fiscale jaren. Ze bieden veel flexibiliteit en kunnen u gemakkelijk helpen de gewenste resultaten te behalen.

Blijf gewoon spelen met offsets en kijk welke andere toepassingen u ervoor kunt hebben. U kunt ook het raadplegen voor andere gevallen waarin de offset-functie nuttig bleek te zijn.

Al het beste,

Melissa