Записки охотника

Ретроспектива

Тематические статьи
Рвав-рвав, собака Смайл поздравляет всех с началом отопительного сезона!

Сегодня мудрый не по годам собакен научит вас «глядеть в прошлое», то есть продемонстрирует на примере построение механизма ретроспективы.

Пример:

  • Таблица «Список сотрудников» содержит необходимые сведения обо всех сотрудниках, когда-либо работавших в организации:

Таблица
Список сотрудников


  • Календарь выделен в качестве отдельной таблицы, при этом он создан «динамическим образом», а именно:

Календарь =
CALENDAR (FIRSTDATE ('Список сотрудников'[Дата приема]), TODAY ())

  • Столбец «Date» переименован в «Дата календаря».

Задача:

На основе представленных данных построить отчет табличного вида, позволяющий оценить количество работающего персонала в определенный момент времени в прошлом.

Рвав-рвав, предварительное описание составлено, теперь можно приступать к построению машины времени!

Процесс разработки:

  • На уровне Power Query cоздано представление таблицы «Список сотрудников», при помощи контекстного меню «Ссылка», имя представления задано как «Даты работы сотрудников».

Таблица
Даты работы сотрудников

  • Для решения поставленной задачи необходимо однозначно понимать, работал ли конкретный сотрудник в определенный момент времени. Для этого в созданное представление добавлен пользовательский столбец «Дата работы», формирующийся следующим образом:

let
EndList = [Дата увольнения] ?? DateTime. Date (DateTime.LocalNow())
in
List. Dates ([Дата приема], Duration. TotalDays (EndList — [Дата приема]) + 1, #duration(1, 0, 0, 0))

Рвав-рвав, есть один тонкий момент: данная формула возвращает список значений с шагом, равным заданному промежутку (одному дню). Поскольку количество значений определяется разницей между начальной и конечной датой списка, дополнительно сделано искусственное приращение на одно значение, так как, если человек устроился сегодня, а уволился завтра, то фактически он отработал два дня, а не один.

  • Далее необходимо «развернуть» сформированные данные в разрезе сотрудника, должности и даты его работы, а также удалить лишнюю информацию. В результате данного комплекса действий, мы получим автоматически обновляемое представление «Даты работы сотрудников» со следующей структурой:

Таблица
Даты работы сотрудников

  • Следующим этапом является расстановка связей, схема модели представлена ниже:


  • После формирования модели данных, можно приступить к дополнительным расчетам, иллюстрирующим полученный результат, в виде, понятном конечному пользователю. Например, можно рассчитать: общее количество персонала, количество персонала, работающего в настоящее время, количество персонала на определенной должности.

Процесс визуализации данных:

  • Создать несколько элементов «Карточка» для визуализации статистической информации по персоналу, используя соответствующие меры.
  • Создать визуальный фильтр «Период», используя элемент «Срез», при этом в качестве значений использовать данные столбца «Дата календаря» таблицы «Календарь». При помощи данного элемента будет происходить процесс фильтрации дат с использованием двусторонней (!) связи.
  • Создать визуальный фильтр «Должность», используя элемент «Срез», при этом в качестве значений использовать данные соответствующего столбца таблицы «Список сотрудников». При помощи данного элемента будет происходить процесс фильтрации сотрудников согласно занимаемой должности.
  • Создать таблицу «Список сотрудников», используя элемент «Таблица», таблица является основным элементом отчета, и содержит полный список сотрудников.
  • Исходя из нашего набора данных, в период с 14 января по 31 декабря 2019 года в компании было 12 сотрудников, 2 сотрудника занимали позицию Старшего менеджера, а 10 — Менеджера, и при этом в настоящий момент в компании работает 8 человек:

Таблица
Полный список сотрудников

Пояснения к отчету:

  • Если все сделано верно, то при изменении конечной даты с «31.12.2019» на «13.02.2019» в таблице «Список сотрудников» будет отображено 2 записи, поскольку ближайшая дата приема 3-го сотрудника — это «14.02.2019», а подобное значение не должно учитываться в расчетах в соответствии с указанным периодом:

Таблица
Проверка отчета номер 1

То есть, по данным построенного отчета, в период с 14.01.2019 по 13.02.2019 в компании работало 2 человека на позиции Старшего менеджера, и при этом они оба продолжают работать в настоящий момент.

  • Фильтр «Период» работает в обе стороны, и вернув конечную дату в значение «сегодня» (или более позднее), а затем изменив значение начального отрезка времени с «14.01.2019» на «01.06.2019», в таблице «Список сотрудников» будет отображено 11 записей, поскольку один из сотрудников покинул компанию накануне (31.07.2019):

Таблица
Проверка отчета номер 2

Фильтрация данных при помощи дополнительных срезов (например, «Должность»), также производится корректным образом, поскольку, при нажатии кнопки «Менеджер», таблица «Список сотрудников» отображает 9 записей, что соответствует значению меры, отображенному в карточке «Сотрудники, всего»:



Рвав-рвав, наша машина времени успешно введена в эксплуатацию, хотя, конечно, смотреть «что у тебя было» — несколько грустновато…
Бегу проверять свои заначки :-)
Ваш Смайл