ich habe ein Problem, das ich nicht lösen kann. Und zwar wie folgt: Im 1. Sheet gibt es eine Liste an Personen mit entsprechenden Vorgängen, die zeitlich beginnen (Spalte L) und zeitlich aufhören (Spalte N). Im 2. Sheet möchte ich pro Monat wissen, wie viele offene Vorgänge in den nächsten Monat mitgenommen werden, wenn in der Spalte N nichts drin steht.
Ein Beispiel: der Antrag von Max Mustermann ging im Februar ein und wird erst im April erledigt. Somit soll bei "Februar" und "März" jeweils eine 1 drin stehen. Erst im "April" soll dann eine 0 stehen, weil im 1. Sheet das Erledigt-Datum auf den 5.4. gesetzt ist.
Alle Einträge im 1. Sheet sollen im 2. Sheet als eine kumulierte Zahl dargestellt werden. Das heißt, im jeweiligen Monat steht nur eine Zahl drin für alle noch offenen Vorgänge.
Ich hoffe, ihr könnt mir folgen, wenn nicht, gerne nachfragen :)
12.09.2024, 08:49 (Dieser Beitrag wurde zuletzt bearbeitet: 12.09.2024, 08:49 von Warkings.)
Wie weit kennst Du Dich mit Powerquery und Powerpivot aus?
Das Ergebnis dieser Powerquery Abfrage ins Datenmodell (Sagt dir das etwas?) laden. Im Datenmodell einen Kalender hinzufügen und eine Beziehung zwischen den Datumsfeldern herstellen. Ein Measure Count:=DISTINCTCOUNT([Name]) anlegen, die richtige Sortierung auf den Monatsnamen anlegen und entsprechend die Pivottabelle einfügen
Code:
let Source = Excel.CurrentWorkbook(){[Name="tbl"]}[Content], chgType = Table.TransformColumnTypes(Source,{{"NAME, Vorname", type text}, {"I.", type any}, {"A.", type any}, {"Maßnahme", type any}, {"Status", type any}, {"Betreuer", type any}, {"von", type any}, {"bis", type any}, {"Eingang am", type any}, {"Vollständig am", type any}, {"z. U.", type any}, {"an x am", type date}, {"Bearb.-Status", type any}, {"erledigt am", type date}}), removeColumns = Table.SelectColumns(chgType,{"NAME, Vorname", "an x am", "erledigt am"}), renameColumns = Table.RenameColumns(removeColumns,{{"NAME, Vorname", "Name"}, {"an x am", "Start"}, {"erledigt am", "Ende"}}), addColumn = Table.AddColumn(renameColumns, "Dates", each if [Ende]=null then List.Dates([Start],Number.From(DateTime.LocalNow())-Number.From([Start]),#duration(1, 0, 0, 0)) else List.Dates([Start],Number.From(Date.StartOfMonth([Ende]))-Number.From([Start]),#duration(1, 0, 0, 0))), extendDates = Table.ExpandListColumn(addColumn, "Dates"), removeColumns02 = Table.RemoveColumns(extendDates,{"Start", "Ende"}), addMonthName = Table.AddColumn(removeColumns02, "Monatsname", each Date.MonthName([Dates]), type text), chgType02 = Table.TransformColumnTypes(addMonthName,{{"Dates", type date}}) in chgType02
Der folgende M-Code erzeugt das Ergebnis direkt, ohne Powerpivot. Damit verliert man IMHO aber Flexibilität bei der Darstellung
Code:
let Source = Excel.CurrentWorkbook(){[Name="tbl"]}[Content], chgType = Table.TransformColumnTypes(Source,{{"NAME, Vorname", type text}, {"I.", type any}, {"A.", type any}, {"Maßnahme", type any}, {"Status", type any}, {"Betreuer", type any}, {"von", type any}, {"bis", type any}, {"Eingang am", type any}, {"Vollständig am", type any}, {"z. U.", type any}, {"an x am", type date}, {"Bearb.-Status", type any}, {"erledigt am", type date}}), removeColumns = Table.SelectColumns(chgType,{"NAME, Vorname", "an x am", "erledigt am"}), renameColumns = Table.RenameColumns(removeColumns,{{"NAME, Vorname", "Name"}, {"an x am", "Start"}, {"erledigt am", "Ende"}}), addColumn = Table.AddColumn(renameColumns, "Dates", each if [Ende]=null then List.Dates([Start],Number.From(DateTime.LocalNow())-Number.From([Start]),#duration(1, 0, 0, 0)) else List.Dates([Start],Number.From(Date.StartOfMonth([Ende]))-Number.From([Start]),#duration(1, 0, 0, 0))), extendDates = Table.ExpandListColumn(addColumn, "Dates"), removeColumns02 = Table.RemoveColumns(extendDates,{"Start", "Ende"}), addMonthName = Table.AddColumn(removeColumns02, "Monatsname", each Date.MonthName([Dates]), type text), sortRows = Table.Sort(addMonthName,{{"Dates", Order.Ascending}}), chgType02 = Table.TransformColumnTypes(sortRows,{{"Dates", type date}}), removeColumns03 = Table.RemoveColumns(chgType02,{"Dates"}), groupRows = Table.Group(removeColumns03, {"Monatsname"}, {{"Liste", each _, type table [Name=nullable text, Monatsname=text]}}), addColumn02 = Table.AddColumn(groupRows, "Liste.1", each List.Distinct([Liste][Name])), addColumn03 = Table.AddColumn(addColumn02, "Anz", each List.Count([Liste.1])), removeColumns04 = Table.RemoveColumns(addColumn03,{"Liste", "Liste.1"}), pivotColumn = Table.Pivot(removeColumns04, List.Distinct(removeColumns04[Monatsname]), "Monatsname", "Anz", List.Sum) in pivotColumn
Wenn ich auch mein vergifteten Senf dazu abgeben darf sind bisher noch keine richtigen Lösungen zusehen hier, unter Annahme das ich es richtig verstanden habe. Anbei meine PQ/Pivot Lösung.
12.09.2024, 12:24 (Dieser Beitrag wurde zuletzt bearbeitet: 12.09.2024, 12:24 von Warkings.)
Der OP muss wissen, was er benötigt
Mit meiner skizzierten Powerquery und Powerpivot-Lösung, erhält man auch die "Summe", die der der OP in seiner Beispieldatei nur als Überschrift genannt hat, ohne dort einen Wert eingetragen zu haben. Ich vermute, er meint die Anzahl der Vorgänge. Das erhält er dann in der Lösung, sofern er sie nachbauen kann, geschenkt
PS Und mehrere Jahre lassen sich auch darstellen PPS
Zitat: unter Annahme das ich es richtig verstanden habe.
Annahmen werden üblicherweise widerlegt, nur Behauptungen werden bewiesen und stimmen.
