Ретроспектива
Рвав-рвав, собака Смайл поздравляет всех с началом отопительного сезона!
Сегодня мудрый не по годам собакен научит вас “глядеть в прошлое”, то есть продемонстрирует на примере построение механизма ретроспективы.
Пример:
Имеется набор данных следующего вида:
сотрудник;
дата приема на работу;
дата увольнения с работы;
признак того, что сотрудник работает в компании в настоящее время;
должность (позиция) сотрудника.
Календарь выделен в качестве отдельной таблицы, при этом он создан “динамическим образом”, а именно:
Calendar =
CALENDAR ( FIRSTDATE ( 'Dataset'[Employment_Date] ), TODAY () )Задача:
На основе представленных данных построить отчет табличного вида, позволяющий оценить количество и качество работающего персонала в определенный момент времени в прошлом.
Рвав-рвав, предварительное описание составлено, теперь можно приступать к построению машины времени!
Решение:
На уровне Power Query cоздано представление таблицы “Dataset”, при помощи контекстного меню “Reference” (“Ссылка”), имя представления задано как “Employee_dates”.
Для решение поставленной задачи необходимо однозначно понимать, работал ли конкретный сотрудник в определенный момент времени. Для этого в созданное представление добавлен пользовательский столбец “Working_Date”, формирующийся следующим образом:
List.Dates ( [Employment_Date],
Duration.TotalDays( [Dismissal_Date] - [Employment_Date] ) + 1,
#duration( 1, 0, 0, 0 ) ) )Рвав-рвав, есть один тонкий момент:
Данная формула возвращает список значений с шагом, равным заданному промежутку (одному дню). Поскольку количество значений определяется разницей между начальной и конечной датой списка, дополнительно сделано искуственное приращение на одно значение, так как, если человек устроился сегодня, а уволился завтра, то фактически он отработал два дня, а не один.
Далее необходимо “развернуть” сформированные данные в разрезе сотрудника, должности и даты его работы, а также удалить лишнюю информацию. В результате данного комплекса действий, мы получим автоматически обновляемое представление “Employee_dates” со следующей структурой:
На этом работы с Power Query завершены, далее необходимо настроить связи между данными:
– связь одностороннего типа “Many to one” (“Многие к одному”) для Employee_dates -> Calendar;
– связь двустороннего типа “Many to one” (“Многие к одному”) для Employee_dates -> Dataset.
После формирования модели данных, можно приступить к дополнительным рассчетам, иллюстрирующим полученный результат, в виде, понятном конечному пользователю. Например, можно рассчитать:
– общее количество персонала;
– количество персонала, работающего в настояцее время;
– количество персонала на определенной должности.
Процесс визуализации данных:
Создать несколько элементов “Card” (“Карточка”) для визуализации статистической информации по персоналу, используя соответствующие меры.
Создать визуальный фильтр “Период”, используя элемент “Slicer” (“Срез”), при этом в качестве значений использовать данные столбца “Date” таблицы “Календарь”. При помощи данного элемента будет происходить процесс фильтрации дат с использованием двусторонней (!) связи”.
Создать визуальный фильтр “Должность”, используя элемент “Slicer” (“Срез”), при этом в качестве значений использовать данные столбца “Position” таблицы “Dataset”. При помощи данного элемента будет происходить процесс фильтрации сотрудников согласно занимемой должности.
создать таблицу “Список сотрудников”, используя элемент “Table” (“Таблица”), таблица является основным элементом отчета, и содержит полный список сотрудников.
Исходя из нашего набора данных, в период с 01 января по 27 сентября 2019 года в компании было 12 сотрудников, 2 сотрудника занимали позицию Старшего менеджера, а 10 – Менеджера, и при этом в настоящий момент в компании работает 8 человек:
Пояснения к отчету:
Если все сделано верно, то при изменении кочечного отрезка времени с “27.09.2019” на “12.02.2019” в таблице “Список сотрудников” должно отобразиться два сотрудника, поскольку ближайшая дата приема 3-го сотрудника – это “14.02.2019”, а подобное значение не должно учитываться в расчетах в соответствии с указанным периодом:
То есть, по данным построенного отчета, в период с 14.01.2019 по 12.02.2019 в компании работало 2 человека на позиции Старшего менеджера, и при этом они оба продолжают работать в настоящий момент.
Фильтр “Период” работает в обе стороны, и вернув конечную дату в значение “сегодня” (или более позднее), а затем изменив значение начального отрезка времени с “14.01.2019” на “01.06.2019”, в таблице “Список сотрудников” должно будет отобразиться 11 записей, поскольку один из сотрудников покинул компанию накануне (31.07.2019):
Фильтрация данных при помощи дополнительных срезов (например, “Должность”), также производится корректным образом, поскольку, при нажатии кнопки “Менеджер”, таблица “Список сотрудников” отображает 9 записей, что соответствует значению меры “Менеджер, всего”, визуализированному при помощи элемента “Card” (“Карточка”).
Рвав-рвав, наша машина времени успешно введена в эксплуатацию, хотя, конечно, смотреть “что у тебя было” – несколько грустновато…
Бегу проверять свои заначки :-)
Ваш Смайл
ДЛЯ ЛЮБИТЕЛЕЙ ПОНАЖИМАТЬ НА КНОПОЧКИ