Gemiddelde van laatste x aantal weken in draaitabel

[MartijnvRijs]

Goedemorgen,

Ik ben al een tijdje op zoek naar een optie om in een draaitabel het gemiddeld aantal orders weer te geven. Met als extra optie om het gemiddelde van de laatste x aantal weken weer te geven.

Zie bijgevoegd bestand.

In de draaitabel staan nu het aantal orders per week per klant weergegeven
Ik wil in plaats van het eindtotaal het gemiddeld aantal orders voor die klant hebben.
Gemiddeld aantal orders heb ik nu handmatig zelf berekend.

Als extra optie wil ik het gemiddeld aantal orders van bijv. de laatste 5 weken weergeven min de laatste week.
Vb: het aantal orders voor klant 5 in week 7 is 100 orders. Ik wil daarnaast voor die klant het gemiddeld aantal orders van de weken 2 t/m 6
En als ik later week 8 toevoeg, wil ik het gemiddeld aantal orders zien van week 3 t/m 7
Etc

Ik hoop dat iemand mij hiermee kan helpen

 

[cow18]

wordt iets gecompliceerder bij jaarovergangen, dus misschien beter iets met PQ.
Nu met een matrixformule.
Het zou nog simpeler kunnen door een extra kolom toe te voegen bij je data, waarbij je dan een 1 zet in die rijen met weeknummerin je gewenste bereik.

 

[JEC.]

Kan ook zonder draaitabel.
Op tabblad 2 een vb t/m week 18. Die kun je zelf uitbreiden. Nog beter zou zijn als je op tabblad 1 een echte tabel zou gebruiken.

 

[JEC.]

Ook nog een optie met power query.
De formule voor de laatste 5 voor de laatste heb ik los toegevoegd

 

[VenA]

@cow18, wat leesvoer nu je toch klaar bent met sneeuwscheppenhttps://www.daxpatterns.com/week-related-calculations/

Hopelijk leest @pixcel deze vraag ook en kan wat licht in de duisternis van PQ en PP en DAX brengen. Ik blijf het maar lastig vinden.

@MartijnvRijs, Sorry voor een beetje inbreken. Jouw vraag is wel een leuk uitgangspunt om 'ons helpers' wat verder te laten verdiepen in alle complexe mogelijkheden die er in Excel te vinden zijn. Een hulpkolommetje links of matrixformule rechts zal best wel werken maar moet eenvoudiger kunnen. Dus dank voor de vraag.

 

[JEC.]

Inderdaad, gewoon een compleet andere taal en methodiek. De basis is heel goed te doen maar zodra je de functies in gaat...

 

[VenA]

maar zodra je de functies in gaat...

Dan zak ik door het ijs.:d

 

[JEC.]

Ik ook

 

[MartijnvRijs]

Bedankt allen
Ik ga kijken welke van de 3 opties die jullie gedeeld hebben voor mij bruikbaar is.

PS: Het is dus sowieso niet in een draaitabel te realiseren?

 

[snb]

Zou daarvoor een tweede draaitabel niet handig zijn, met slicer en een aantal aggregate-funkties ?

 

[MartijnvRijs]

wordt iets gecompliceerder bij jaarovergangen, dus misschien beter iets met PQ.
Nu met een matrixformule.
Het zou nog simpeler kunnen door een extra kolom toe te voegen bij je data, waarbij je dan een 1 zet in die rijen met weeknummerin je gewenste bereik.

Beste cow18,

Ik heb de formule gekopieerd naar mijn oorspronkelijke bestand en de verwijzingen aangepast naar de juiste cellen, maar nu werkt de formule niet meer. Ik krijg een 0 als antwoord. Waar kan dat mee te maken hebben? Heeft dat met de matrixformule te maken? Wat kan ik daaraan doen?

Extra vraag: als ik nu het gemiddelde van de laatste 5 weken wil wijzigen in bijv de laatste 8 of de laatste 10 weken. Wat moet ik dan in de formule wijzigen?

 

[pixcel]

Dit komt denk ik een eind in de richting. Het rollend weekgemiddelde is over 3 weken.

PS1: als je testdata geeft is het voor helpers wel fijn als daar geen fouten in zitten. In je originele bestand zit week 5 in maand 1 en in maand 2.
PS2: leuk om de toenemende nieuwsgierigheid naar de Power omgeving te zien

 

[VenA]

@pixcel,
Dank je. Zoals in de handtekening van @snb 'VBA is een taal die je moet leren met een grammatica- en een woordenboek.' is dit ook wel een aparte taal. Gaat wel een paar uurtjes studie kosten om het te begrijpen:d

 

[cow18]

2 manieren, 1 met een draaitabel en 1 met een dictionary

 

[MartijnvRijs]

Dankjewel cow18

Maar het is nog niet hoe ik het wil hebben, sorry ;-)

Ik vond de formule prima werken zoals je in eerste bericht hebt gedeeld. Enige probleem is dat ik deze niet werkend krijg in het originele bestand. Zie printscreen van de formule die ik heb toegevoegd. Wat doe ik verkeerd?

En
Extra vraag: als ik nu het gemiddelde van de laatste 5 weken wil wijzigen in bijv de laatste 8 of de laatste 10 weken. Wat moet ik dan in de formule wijzigen?

Sorry dat ik nog niet zo snel tevreden ben

 

[cow18]

1. het is een matrixformule, dus afsluiten doe je met CTRL+SHIFT+ENTER ipv de gewone enter
2. die -6 en die 2 keer 5 moet je vervangen door een bepaalde cel. Dus stel dat je in AA1 het aantal weken invult, dan vervang je die -6 door -(AA1+1) en die 5 door AA1.

Toch dacht ik dat de 2e oplossing met VBA gemakkelijker zou zijn, daarmee ga je over de jaarovergang heen, anders is het sukkelen de 1e zoveel weken van het nieuwe jaar.