Tutorial: Monatsverteilung mit Formeln erstellen

Aufgabenbeschreibung

Immer wieder erfolgen Planungen in Zeiträumen (von-bis), die dann auf die Zeitachse verteilt werden sollen. Wir unterstellen hier ein einfaches Beispiel einer Projektplanung, in der Personentage über einen Zeitraum geplant werden.

Projekt	von	bis	PT
Alpha	14.03.2025	21.05.2025	30
Beta	01.05.2025	17.07.2025	70
Gamma	10.06.2025	15.08.2025	60
Delta	03.08.2025	21.08.2025	10

Im Ergebnis sollen die oben angegebenen Personentage (PT) auf Monate verteilt werden und zwar anteilig nach Kalendertagen:

Projekt	Mrz	Apr	Mai	Jun	Jul	Aug	Gesamt
Alpha	7,83	13,04	9,13				30
Beta			27,82	26,92	15,26		70
Gamma				18,81	27,76	13,43	60
Delta						10	10

Für das Projekt Alpha berechnen sich die Werte wie folgt:

• Das Verhältnis zwischen geplanten Tagen (30) und Gesamtkalendertagen zwischen 14.03. und 30.06. (69) lautet => 43,48%
• In den März fallen ab 14.03. insgesamt 18 verbleibende Kalendertage (31-13). Die für März geplanten Tage lauten dann: 18 * 43,48% => 7,83
• Im April beträgt der Wert 30 * 43,48% => 13,04
• Im Mai beträgt der Wert 21 * 43,48% => 9,13

Vorbereitung

Im CoPlanner werden dafür zunächst zwei Tabellen angelegt:

Hier werden die Projekte geplant:

TBL_Projektplanung

Feld	Typ	Beschreibung
Projekt	Dimension	Projekt-ID
PT	Dezimalzahl	die geplanten Tage
von	Datum	von-Datum
bis	Datum	bis-Datum

Hier wird die Monatsverteilung gespeichert:

TBL_Projektplanung_monatlich

Feld	Typ	Beschreibung
Projekt	Dimension	Projekt-ID
Zeit	Dimension	Monats-ID
PT	Dezimalzahl	PT je Monat

Schritt 1: Vorberechnungen in der Quelltabelle

Für eine einfachere Lesbarkeit der weiteren Schritte empfiehlt es sich, einige Berechnungen direkt in der Quelltabelle vorzunehmen, z. B. ist im Hinblick auf notwendige Datumsfunktionen eine einmalige Umwandlung der von- und bis-Daten in eine CoPlanner-Zeit-ID sinnvoll.

Tabellenspalten ergänzen

Wir ergänzen die Quelltabelle daher um die folgenden, zunächst leeren Spalten:

Erweiterung TBL_Projektplanung

Feld	Typ	Beschreibung
Tage_von	Dimension	Dimension Tage: Abbildung des von-Datums als CoPlanner-Zeit-ID
Tage_bis	Dimension	Dimension Tage: Abbildung des bis-Datums als CoPlanner-Zeit-ID
Kalendertage_Gesamt	Dezimalzahl	Anzahl der gesamten Kalendertage im Zeitraum
Kalendertage_Monat_von	Dezimalzahl	Anzahl der Kalendertage im ersten Monat (abgeleitet aus dem von-Datum)
Kalendertage_Monat_bis	Dezimalzahl	Anzahl der Kalendertage im letzten Monat (abgeleitet aus dem bis-Datum)

Formel für CoPlanner-Zeit-IDs

Hinweis  In allen folgenden Formel-synthaxbeispielen sind die Zeilen, die mit dem doppelten Minus - - gekennzeichnet sind, Kommentare. Bei Verwendung als Kopiervorlage bzw. Verwendung der Beispiele in CoPlanner dürfen diese Kommentare nicht mit in den CoPlanner eingefügt werden.

Um zuerst die Felder Tage_von und Tage_bis zu befüllen, erstellen wir die Formel CAL_Projektplanung_UPDATE_Zeit und wechseln in den Formeldesigner durch einen Klick auf Entwurf (Web).

Wir wählen unter Allgemein "Datensatz aktualisieren" und ziehen in Zuweisungen die Tabelle TBL_Projektplanung auf Quelle oder Ziel. Den Feldern Tage_von und Tage_bis weisen wir jeweils einen Ausdruck zu, den wir wie dargestellt berechnen.

--Tage_von: Mit einer simplen Umwandlung in eine CoPlanner-Zeit-ID wird die ID für den Tag (Parameter "D") ermittelt.
FKT_TIMEDIMFROMDATE('D',[TBL_Projektplanung.von])

--Tage_bis: analog zu oben
FKT_TIMEDIMFROMDATE('D',[TBL_Projektplanung.bis])

Die Formel wird dann aktiv gesetzt und gespeichert.

Formel für Kalendertage

Um die restlichen drei neuen Felder zu befüllen, erstellen wir eine weitere Formel CAL_Projektplanung_UPDATE_Kalendertage und wechseln in den Formeldesigner durch einen Klick auf Entwurf (Web).

Wir wählen unter Allgemein wieder "Datensatz aktualisieren" und ziehen in Zuweisungen die Tabelle TBL_Projektplanung auf Quelle oder Ziel. Den Kalendertage-Feldern weisen wir jeweils einen Ausdruck zu, den wir wie dargestellt berechnen.

--Kalendertage_Gesamt: Hier wird die Differenz an Tagen (Parameter "D") zwischen von und bis ermittelt und ein Tag addiert, damit der letzte Tag auch berücksichtigt wird.
FKT_TIMEDIMDIFF(D,[TBL_Projektplanung.Tage_bis],[TBL_Projektplanung.Tage_von])+1

--Kalendertage_Monat_von: Hier wird ein Monat (Parameter "M") addiert und dann die Differenz an Tagen (Parameter "D") gezogen.
FKT_TIMEDIMDIFF('D',
	FKT_TIMEDIMADD('M',1,[TBL_Projektplanung.Tage_von]),
	[TBL_Projektplanung.Tage_von]
)

--Kalendertage_Monat_bis: Hier wird der Tag der Zeit-ID ermittelt (Parameter "D"). 
FKT_TIMEDIMDATEPART('D',[TBL_Projektplanung.Tage_bis])

Mittels "Optionale Felder" sollte die Priorität bei Ausführung auf eine Zahl größer der vorigen Formel gestellt werden (Beispiel: Diese Formel auf -10 und die vorherige Formel auf -11). Damit ist sichergestellt, dass die Felder Tage_von und Tage_bis bereits richtig berechnet sind, da diese in dieser Formel hier weiterverwendet werden.

Die Formel wird dann aktiv gesetzt und gespeichert.

Zwischenergebnis

Die kalkulierten Werte in CoPlanner sehen dann so aus:

Tatsächlich sind die Werte für Projekte, deren von- und bis-Datum im selben Monat liegen, nicht sehr hilfreich - sie werden im Weiteren für diesen Ausnahmefall aber auch nicht benötigt.

Schritt 2: Berechnen der Monatswerte

Nun werden mit einer weiteren Formel die Monatswerte in der Ergebnistabelle TBL_Projektplanung_monatlich ermittelt.

Formel für die Monatsverteilung

Wir erstellen die Formel CAL_PROJEKTPLANUNG_GROUP_monatlich und wechseln in den Formeldesigner durch einen Klick auf Entwurf (Web).

Wir wählen wir unter Allgemein "Summiertes Weiterschreiben" und ziehen in Zuweisungen die Tabelle TBL_Projektplanung auf Quelle und die Tabelle TBL_Projektplanung_monatlich auf Ziel.

Mittels "Dimensionen verknüpfen" im Menü links stellen wir eine Beziehung zwischen gleichen Dimensionen her, in diesem Fall für die Dimension Projekte, die in beiden Tabellen vorhanden ist.

Da die Datensätze aus der Quelltabelle nun auf Monate innerhalb der Projektdauer verteilt werden sollen, ergänzen wir noch die Dimension Zeit in den Tabellen. Ziehen Sie die das Element Zeit dafür einfach in die Mitte zwischen die beiden schon vorhandenen Tabellen. Nun verknüpfen wir das Feld COPSYS_ID der Dimension Zeit mit der Zeit in der Zieltabelle.

Ohne weitere Schritte würde eine solche Formel nun alle Datensätze aus der Tabelle TBL_Projektplanung mit den Datensätzen aus der Dimension Zeit aufspannen. Wir wollen jetzt sicherstellen, dass dies nur für Monate passiert, die zwischen von- und bis-Datum liegen. Dazu aktivieren wir die Option "Optionale Felder" links unten und wechseln zu Auslöser und Bedingungen.

Unter den Bedingungen ergänzen wir jeweils mit Klick auf + Neue Bedingung folgende Einträge:

-- Da in der Dimension Zeit nicht nur Monate, sondern auch Jahre und weitere Elemente enthalten sind, schränken wir auf nur Monate ein. Die Formel retourniert nur für Monate ein M.
FKT_TIMEDIMTYPEFLAG([Zeit.COPSYS_ID]) = 'M'

-- Der Monat muss größer oder gleich dem Monat, in dem das von-Datum liegt, sein.
[Zeit.COPSYS_ID]  >= FKT_TIMEDIMADD('M',0,[TBL_Projektplanung.Tage_von])

-- Der Monat muss kleiner oder gleich dem Monat, in dem das bis-Datum liegt, sein.
[Zeit.COPSYS_ID]  <= FKT_TIMEDIMADD('M',0,[TBL_Projektplanung.Tage_bis])

Bitte beachten Sie auch die Auswahl des richtigen Datentyps rechts - für den ersten Vergleich lautet dieser Text, bei den anderen beiden wird der Defaultwert Ganzzahl verwendet.

Die automatisch vergebene Priorität bei Ausführung kann bei 0 belassen bleiben - wichtig ist nur, dass die beiden vorigen Formeln davor ausgeführt wurden.

Nun fehlt nur noch die korrekte Ermittlung der Personentage je Monat. Dafür wechseln wir zurück in das Register Zuweisungen und ziehen einen Ausdruck auf das Feld PT im Ziel.

--PT: In den folgenden Kommentaren wird die Formel erklärt - sollten Sie diese in den CoPlanner einfügen wollen, verwenden Sie bitte den Ausdruck im folgenden Kasten.

-- Für den Sonderfall, dass der Zeitraum nur in einen Monat fällt, entfällt die Notwendigkeit der Verteilung, also 100% (Faktor 1) der PT entfallen auf diesen einen Monat.
IIF(
		FKT_TIMEDIMADD('M',0,[TBL_Projektplanung.Tage_von])=FKT_TIMEDIMADD('M',0,[TBL_Projektplanung.Tage_bis]),
		1,
-- für die anderen Fälle unterscheiden wir jetzt, ob wir im ersten, letzten oder einem Monat dazwischen liegen
-- Im ersten Monat ziehen wir die oben ermittelten Kalendertage des ersten Monats heran
		IIF(
			[Zeit.COPSYS_ID]=FKT_TIMEDIMADD('M',0,[TBL_Projektplanung.Tage_von]),
			[TBL_Projektplanung.Kalendertage_Monat_von],
-- Im letzten Monat werden die oben ermittelten Kalendertage des letzten Monats verwendet
			IIF(
				[Zeit.COPSYS_ID]=FKT_TIMEDIMADD('M',0,[TBL_Projektplanung.Tage_bis]),
				[TBL_Projektplanung.Kalendertage_Monat_bis],
-- in den Monaten dazwischen werden alle Kalendertage des Monats verwendet. Um diese zu ermittelt, wird aus Monatsletzten der Tag extrahiert (FKT_TIMEDIMDATEPART). Der Monatsletzte eines Monats wird ganz einfach durch die Addition eines Monats und dann die Subtraktion eines Tages ermittelt.
				FKT_TIMEDIMDATEPART('D',FKT_TIMEDIMADD('D',-1,FKT_TIMEDIMADD('M',1,[Zeit.COPSYS_ID])))
				)	
			)
-- Um den Anteil jedes Monats zu ermitteln, wird jetzt durch die oben ermittelten Gesamtkalendertage dividiert. Aus sorgfältiger Praxis verhindern wir eine Division durch 0 durch eine Bedingung (IIF).
			/ IIF([TBL_Projektplanung.Kalendertage_Gesamt]=0,1,[TBL_Projektplanung.Kalendertage_Gesamt])
	)
-- Schließlich multiplizieren wir den Anteil mit den tatsächlich zu verteilenden Personentagen (PT)
*[TBL_Projektplanung.PT]

Kopiervorlage

IIF(
		FKT_TIMEDIMADD('M',0,[TBL_Projektplanung.Tage_von])=FKT_TIMEDIMADD('M',0,[TBL_Projektplanung.Tage_bis]),
		1,
		IIF(
			[Zeit.COPSYS_ID]=FKT_TIMEDIMADD('M',0,[TBL_Projektplanung.Tage_von]),
			[TBL_Projektplanung.Kalendertage_Monat_von],
			IIF(
				[Zeit.COPSYS_ID]=FKT_TIMEDIMADD('M',0,[TBL_Projektplanung.Tage_bis]),
				[TBL_Projektplanung.Kalendertage_Monat_bis],
				FKT_TIMEDIMDATEPART('D',FKT_TIMEDIMADD('D',-1,FKT_TIMEDIMADD('M',1,[Zeit.COPSYS_ID])))
				)	
			)
sorgfältiger Praxis verhindern wir eine Division durch 0 durch eine Bedingung (IIF).
			/ IIF([TBL_Projektplanung.Kalendertage_Gesamt]=0,1,[TBL_Projektplanung.Kalendertage_Gesamt])
	)
*[TBL_Projektplanung.PT]

Nun setzen wir die Formel noch aktiv und speichern sie.

Die Meldung beim Speichern können wir mit Ja bestätigen. Sie macht uns darauf aufmerksam, dass wir die beiden Quelltabellen über keine direkten Merkmale verbunden haben. Das haben wir tatsächlich nur indirekt durch die Einschränkung in den Filtern der Zeit zwischen von- und bis-Datum getan.

Nach erneutem Speichern unserer Testdatensätze in der Tabelle TBL_Projektplanung erhalten wir in der Tabelle TBL_Projektplanung_monatlich folgendes Ergebnis:

😊 Viel Freude bei der Umsetzung oder Adaptierung für Ihren konkreten Bedarf!