Редактор формул

Компонент «Редактор формул» обеспечивает пользователю-администратору данных удобный интерфейс для ввода и редактирования формул расчета вычисляемых показателей. В качестве аргументов формулы используются показатели, к которым могут быть применены условия фильтрации по аналитическим признакам, или константные значения.

Содержание

Основные понятия

  • Показатель — свойство объекта, представляющее какую-либо его характеристику. Подробнее см. раздел Показатели.
  • Значение показателя — значение свойства объекта.
  • Цепочка показателей — последовательность показателей, каждый следующий элемент которой является свойством значения предыдущего показателя.
  • Значение цепочки показателей — значение последнего показателя цепочки.
  • Фильтр — правило, ограничивающее множество объектов, обладающих некоторым свойством, заданием ограничения на значение другого свойства этого объекта. Фильтр задается цепочкой показателей, условием и ограничивающим значением.
  • Исходные данные — значения показателей, хранящиеся в базе данных системы.
  • Источник — цепочка показателей, значение которой является результатом вычисления или запроса исходных данных.

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

Определение значений источников

Значением источника при заданных фильтрах является соответствующее значение из базы данных. Если значения в базе данных нет, то значение вычисляется.

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

Формулы

  • Формула - выражение в виде равенства.
  • Аргумент формулы - источник значений, возможно (не обязательно) ограниченный фильтрами.

Формула состоит из левой и правой частей. В левой части формулы содержится аргумент, источник значений которого - показатель, которому принадлежит эта формула. В фильтрах левой части разрешено соединять условия только логической операцией «И». В правой части формулы содержатся аргументы (пары показатель-фильтр), соединенные арифметическими операциями и функциями.

Для создания или изменения формулы расчета показателя в карточке показателя предусмотрено окно редактирования формул (подробнее см. подраздел Задание формул и агрегации). В окне редактирования формул названия показателей - источников данных (аргументов формулы) подсвечиваются синим цветом, а условия фильтров - зеленым.

Примеры простых формул:

  1. Остаток = Доход - Расход
  2. Баланс ПРИ Статья баланса = Остаток = Баланс ПРИ Статья баланса = Доход - Баланс ПРИ Статья баланса = Расход
  3. Баланс ПРИ (Статья баланса = Остаток И Период = 2018 г.)= Баланс ПРИ (Период = 2017 г. И Статья баланса = Остаток) - Баланс ПРИ (Статья баланса = Расход И Период = 2017 г.)
  4. Доля расходов на производство, % = 100.0 * (Расход ПРИ Статья баланса = Производство) / Расход

Вычисление значения показателя по формуле сводится к выполнению следующих этапов:

  • Обработка аргументов формулы - определение источников и их ограничений (фильтров), от которых зависит вычисление значений исходного источника.
  • Определение значений источников с учетом их ограничений.
  • Формирование значений источников арифметическими операциями, операциями сравнения и функциями, заданными в формуле.

Источниками значений из правой части формулы являются цепочки показателей, заданных на аргументах, а их фильтры определяются сочетанием фильтров исходного источника и фильтров аргумента.

Дополнительные способы определения значений источников аргументов

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

Свободные переменные

Свободные переменные позволяют не указывать конкретное значение в условиях фильтров, вместо конкретного значения задается символ переменной - буква латинского алфавита: X, Y, Z и т.п. Например:

Показатель 1 ПРИ Административная структура = X = Показатель 2 ПРИ Административная структура = X

Чтобы задать свободную переменную, при редактировании фильтра в формуле выберите опцию «Задание переменной».

_images/196.png

Значение по умолчанию

Позволяет указать для аргументов значения по умолчанию, тогда формула будет вычислена, даже если какие-то аргументы не определены. Для задания значения по умолчанию при задании агрумента формулы в поле «Значение по умолчанию» нужно ввести/выбрать значение по умолчанию.

_images/193.png

«Показатели по цепочке»

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

Примеры формул с использованием «показателей по цепочке»:

Наименование вышестоящей организации = Административная структура.Родительский элемент в дереве.Полное наименование

Дата подписания контракта = Контракт.Дата подписания

Количество контрактов, заключённых в мае 2017 г. = КОЛИЧЕСТВО (Контракт ПРИ (Контракт.Дата подписания > 30.04.2017 И Контракт.Дата подписания < 01.06.2017))

Формулы с использованием «показателей по цепочке» задаются только для невычисляемых показателей, то есть рассчитываются только по исходным данным, не по вычисленным.

Формулы с текущим периодом

Текущий период - это специальный фильтр для периодов, который часто используется в формулах. При обычном фильтре на период пользователь может выбрать тип периода и его начало. Но кроме обычных типов периодов (день, месяц, квартал, год и т.п.) есть специальные типы «текущий день», «текущий месяц» и т.п. Для таких типов нужно указать не начало периода, а сдвиг по периоду (0, +1, -1 или любое другое целое число).

_images/tec_period.png

Несколько примеров формул с использованием текущих периодов:

Прирост урожая, % ПРИ Период = Текущий год = 100.0 * Урожай ПРИ Период = Текущий год / Урожай ПРИ Период = Текущий год - 1 Количество на начало периода ПРИ Период = Текущий квартал = Количество на конец отчетного периода ПРИ Период = Текущий квартал - 1

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

Значения из фильтров

Если в формуле надо использовать значение показателя, который используется в качестве фильтра в отчете, то при задании аргумента у него можно выбрать опцию «Брать значение из фильтров:»

_images/197.png

Прогноз ПРИ Период = Текущий день = Количество ПРИ Период = Текущий месяц / ДОЛЯ_В_МЕСЯЦЕ(Период (из фильтров) ПРИ Период = Текущий день)

В данном случае аргументом функции ДОЛЯ_В_МЕСЯЦЕ будет период, значение которого - текущий день из фильтров формулы. В формуле с аргументом с признаком «Брать значение из фильтров» обязательно должен присутствовать аргумент без этого признака.

Рекурсивные формулы

Использование свободных переменных и текущих периодов дает возможность задания рекурсивных формул для вычисления значений показателей.

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

Количество на начало периода ПРИ Период = 1999 г. = 0

Количество на начало периода ПРИ Период = Текущий квартал = Количество на конец отчетного периода ПРИ Период = Текущий квартал -1

Агрегация

Агрегация - это частный случай рекурсивных формул со свободными переменными. Например, в случае выбора функции агрегации «Сумма» выполняется расчет по формуле:

Показатель ПРИ Признак = X = СУММА (Показатель ПРИ Признак.Родительский элемент в дереве = X)

Показатель «Родительский элемент в дереве» (id = -400) - системный показатель, используется для древовидных справочников и периодов, указывает на родительский узел в дереве.

Рекурсивные формулы со сдвигом свободных переменных

Рекурсивные формулы с текущим периодом со сдвигом вида

Показатель ПРИ Период = Текущий год = Показатель ПРИ Период = Текущий год - 1`

являются специальным случаем обобщенных рекурсивных формул со сдвигом свободных переменных

Показатель ПРИ Период = X = Показатель ПРИ Период = X - 1`

Использование текущего периода дополнительно ограничивает тип периода, по которому происходит рекурсия. Базовый вариант для рекурсивных формул со сдвигом задавать не надо, рекурсия сама определит, когда остановиться.

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

Формулы для вычисления виртуальных признаков

Виртуальные признаки также являются частным случаем формул со свободной переменной, так, формула:

Показатель1 = Показатель2.Показатель1

эквивалентна использованию следующей формулы у показателя, для которого требуется применить «виртуальный признак»

Показатель3 ПРИ Показатель1 = X = Показатель3 ПРИ Показатель2.Показатель1 = X

Совместимость типов данных в формуле

Для возможности вычисления формулы у ее элементов (аргументов, констант, результатов вычисления функций) должны быть совместимые типы данных.

Совместимость типов элементов формулы означает допустимость применения заданной операции или функции к элементам данных типов. При этом операция или функция также «порождает» элемент формулы с определенным типом данных.

Проверка совместимости типов левой и правой частей формулы производится по тем же правилам, что и проверка совместимости для операции сравнения «==» (см. ниже).

Арифметические операции и операции сравнения

В формулах допустимо использование арифметических операций «+», «-«, «*», «/». При этом вcе числовые типы (целое, число, дробное десятичное, булево) в этих операциях совместимы между собой.

Для операций «-» и «+» дополнительно совместимы типы «период» и «целое». Например, допустимы выражения:

Период1 + 2

Период1 - 1

Результатом применения операции является период того же типа, что и значение операнда типа «Период» со сдвигом в число шагов периода, равному значению операнда типа «Целое» (т.е. +2 дня, -1 месяц).

Для операции «+» дополнительно совместимы между собой операнды с типом строка. Результатом применения операции «+» к строкам является конкатенация (объединение в одну строку) этих строк.

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

В формулах допустимо использование бинарных операций сравнения «==», «<», «<=», «>», «>=».

Во всех операциях сравнения все числовые типы совместимы между собой. Нечисловые типы допустимо использовать только с операцией «==» и только если оба операнда имеют одинаковый тип. При этом для типов со значениями из справочников (множество, дерево) одинаковость означает, помимо совпадения типов, совпадение справочников, на которые ссылаются операнды с рассматриваемыми типами.

Функции

Функции используются для выполнения преобразований значений показателей-источников данных, которые нельзя выразить (или выразить ёмко) арифметическими операциями и операциями сравнения.

Каждая функция имеет свои правила работы с типами элементов.

Функции делятся на два типа:

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

Обычные функции

Вот список обычных (не жадных) функций (в формате «функция (количество аргументов) - описание»):

  • RANK (1) - Вычисление ранга значения показателя среди его обязательных аналитических признаков.
  • ПОДСТРОКА (3) - Получение подстроки из строки с разделителями. Первый аргумент - исходная строка, второй аргумент - разделитель, третий аргумент - индекс (порядковый номер) возвращаемого отрезка (индекс первого отрезка - 1).
  • ЛОГ (1, 2) - Вычисление логарифма. Принимает один или два аргумента. Первый аргумент - показатель, от значений которого вычисляется логарифм. Второй аргумент - основание логарифма. Если второй аргумент не задан, то вычисляется натуральный логарифм. Доступна с релиза 1.40.0.
  • НАЧАЛО_ПЕРИОДА (1) - Дата начала периода.
  • ЦЕЛОЕ (1) - Округление числа до меньшего целого и преобразование строки в целое.
  • ДРОБНОЕ_ДЕСЯТИЧНОЕ (1) - Приведение аргумента к типу «Денежный».
  • СТР_ДАТА (2) - Форматирование даты в строку. Первый аргумент - дата, второй аргумент - строка, содержащая латинские символы форматирования через свой разделитель: E (название месяца), j (день месяца), n (номер месяца), t (число дней в месяце), w (день недели, где 0 - воскресенье), W (неделя года), y (последние две цифры года), Y (год), z (день года) и другие.
  • ДОЛЯ_В_МЕСЯЦЕ (1) - Доля периода типа «День» (аргумента функции) в месяце, который его содержит (для первого дня месяца доля равна нулю).
  • МИН_ИЗ (произвольное) - Минимальное из значений аргументов функции.
  • ЕСТЬ (1) - Есть одно значение (выражение-аргумент этой функции имеет одно значение).
  • РАБОЧИЙ_ДЕНЬ (1) - Период с тем же типом, что и период-аргумент, и началом в ближайший рабочий день позже периода-аргумента.
  • РАБОЧИЙ_ДЕНЬ_ДО (1) - Период с тем же типом, что и период-аргумент, и началом в ближайший рабочий день раньше периода-аргумента.
  • ЭКСП (1, 2) - Вычисление показательной функции. Принимает один или два аргумента. Первый аргумент - показатель, в значение которого (показатель степени) возводится число из второго аргумента (основания степени). Если основание степени не задано, то оно равно числу e - основанию натурального логарифма. Доступна с релиза 1.40.0.
  • СТР (1) - Приведение аргумента к типу «Строка».
  • ДЛИНА_КВАРТАЛА (1) - Количество дней в квартале, в который попадает заданная дата (или начало заданного периода): «ДЛИНА_КВАРТАЛА ( Период или Дата )».
  • С_НАЧАЛА_КВАРТАЛА (1) - Количество дней между датой, заданной аргументом, (или началом периода, заданного аргументом), и началом квартала, в который входит эта дата (или начало периода): «С_НАЧАЛА_КВАРТАЛА (Период или Дата)».
  • ОКРУГЛ (1) - Округление числа до ближайшего целого.
  • ДЛИНА_ПЕРЕСЕЧЕНИЯ (3) - Длина пересечения - один интервал задается первыми двумя аргументами-периодами, второй интервал - третьим аргументом-периодом.
  • ЕСЛИ (2, 3) - Принимает два или три аргумента. Первый аргумент - логическое выражение. Если оно истинно, возвращает второй аргумент, если ложно - то третий аргумент (в варианте с двумя аргументами в этом случае считаем, что значение не вычислено).
  • ДЛИНА_ГОДА (1) - Количество дней в году, в который попадает заданная дата (или начало заданного периода): «ДЛИНА_ГОДА ( Период или Дата )».
  • ПЕРИОД (1, 2) - Преобразование даты к периоду. Первый аргумент - дата, второй - строка с типом периода, по умолчанию (если тип периода не задан) - «День».
  • МАКС_ИЗ (произвольное) - Максимальное из значений аргументов.
  • В_ДОЛЕ - Фильтрация значений, которые входят по рангу в определенную долю общей суммы. Первый аргумент - показатель, значения которого фильтруются, второй аргумент - доля (число от 0 до 1).

Макросы (аналогичны в применении формулам, но раскрываются в ходе вычислений в обычные формулы, что видно при просмотре информации о вычислении):

  • СКОЛЬЗЯЩЕЕ_СРЕДНЕЕ (3) - Среднее значение первого показателя-аргумента за несколько предыдущих значений второго показателя-аргумента. Третий аргумент - число предыдущих значений.

Далее некоторые функции описаны подробнее.

Функция СТР_ДАТА

Если нужно получить строку, представляющую дату в обычном российском формате, задайте формат «d.m.Y»:

Строка_Дата заключения контракта = СТР_ДАТА ( Дата заключения контракта , «d.m.Y»).

Результаты применения данной формулы:

_images/formula_str_date1.png

Если нужно получить строку, представляющую дату в российском формате, но без ведущего нуля при записи числа, задайте формат «j.m.Y»:

Строка_Дата заключения контракта = СТР_ДАТА ( Дата заключения контракта , «j.m.Y»).

Результаты применения данной формулы:

_images/formula_str_date2.png

Если нужно получить строку текста с вставленными в нужных местах названиями числа, месяца и года заключения контракта, используйте несколько вызовов функции СТР_ДАТА, например:

Строка_Сведения о контракте = (((((«Год заключения контракта: » + СТР_ДАТА ( Дата заключения контракта , «Y»)) + «; Число и месяц заключения контракта: » ) + СТР_ДАТА ( Дата заключения контракта , «j»)) + » » ) + СТР_ДАТА ( Дата заключения контракта , «M»)).

Результаты применения данной формулы:

_images/formula_str_date3.png

Если необходимо получить строку в формате: день, месяц и год из показателя типа «Дата и время»:

Дата заказа = ((((СТР_ДАТА ( Дата и время заказа , «j») + «число») + СТР_ДАТА ( Дата и время заказа , «m»)) + «месяц») + СТР_ДАТА ( Дата и время заказа , «Y»)) + «год».

Результаты применения данной формулы:

_images/date_time.PNG

Форматирование дня месяца и номера месяца может выводиться двумя способами. Например, дата 02.05.2009. При «j» день месяца «2» , а при «d» получаем «02». При «n» номер месяца «5» , а при m получаем «05».

Функции ЕСЛИ и ЕСТЬ

Функция ЕСЛИ принимает два или три аргумента. Первый аргумент - логическое выражение с использованием оператора сравнения («==», «<», «<=», «>», «>=»). Если оно истинно, функция возвращает второй аргумент. Если логическое выражение ложно - функция возвращает третий аргумент (а в варианте с двумя аргументами в этом случае нет значения).

Нужно быть внимательным при использовании функций ЕСЛИ и ЕСТЬ для аргументов, которые логически являются признаками. Между выражениями:

ЕСЛИ ( Период = 10, Значение )

ЕСТЬ ( Значение ПРИ Период = 10 )

есть очень существенная разница. В первом выражении «Период» выступает как один из аргументов, а во втором - как условие на признак. Поэтому в первом выражении объекты, у которых есть значения «Период» и «Значение» могут быть различными (и вероятно будут, если не будет расслоения).

Т.е. первое выражение означает - с некоторыми общими признаками (контекст вычисления в текущей ячейке) есть объект, у которого есть значение «Период», равное 10, и есть объект, у которого есть значение «Значение».

У второй же формулы смысл другой - что при признаке «Период» равном 10, мы берем значение «Значение» - т.е. это будет один (пусть и вычисленный) объект.

Таким образом, не нужно использовать функцию ЕСЛИ c признаками, если вы точно не уверены, что происходит. Основной случай - это использование ЕСЛИ с аргументами, когда мы хотим выбрать одно из значений в зависимости от других значений.

Для объединения двух логических условий в первой части выражения с «ЕСЛИ» используется знак «*» вместо «И». Вместо «ИЛИ» используется знак «+».

Функции от показателей типа «Период»

Для показателей типа «Период» работают формулы сложения и вычитания:

Период_день2 = Период_день1 + 2

Период_месяц2 = Период_месяц1 - 1

возвращают период того же типа со сдвигом в число шагов периода, указанное в формуле (т.е. +2 дня, -1 месяц)

Функция, актуальная для дней:

Период_день2 = РАБОЧИЙ_ДЕНЬ (Период_день1)

возвращает ближайший рабочий день в будущем - понедельник (пн), если итоговое Период_день1 выпало на субботу (сб) или воскресенье (вс).

Период_день2 = РАБОЧИЙ_ДЕНЬ_ДО (Период_день1)

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

Число = ДОЛЯ_В_МЕСЯЦЕ (Период)

возвращает долю дня в месяце (для первого числа месяца доля равна 0).

Число = ДЛИНА_ПЕРЕСЕЧЕНИЯ (Период1, Период2, Период3)

возвращает длину пересечения двух интервалов - (п1.начало, п2.начало) и (п3.начало, п3.конец). То есть первый временной интервал - от начала периода Период1 до начала периода Период2, второй временной интервал - от начала периода Период3 до конца периода Период3.

Функции от показателей типа «Дата»

Пусть есть показатель «А», который принимает численные значения, и показатель «Дата», принимающий значения типа «Дата и время».

  1. Формула

A = С_НАЧАЛА_КВАРТАЛА (Дата)

определяет, в какой квартал попала дата - аргумент функции, и возвращает разницу в днях между аргументом и датой начала квартала.

  1. Формула

А = ДЛИНА_КВАРТАЛА (Дата)

определяет, в какой квартал попала дата - аргумент функции, и возвращает разницу в днях между датой конца квартала и датой начала квартала.

Даты начала и конца кварталов:

  • 1 января – 31 марта.
  • 1 апреля – 30 июня.
  • 1 июля – 30 сентября.
  • 1 октября – 31 декабря.
  1. Формула

А = ДЛИНА_ГОДА (Дата)

вычисляет, сколько дней в году, соответствующем данной дате.

Функции ДЛИНА_КВАРТАЛА и ДЛИНА_ГОДА также работают с аргументами типа «Период». Соответствующий квартал или год определяется в зависимости от даты начала периода.

Над функциями от показателей типа «Дата» можно производить математические операции. Если аргументы у функций одинаковые, то сумма, разность, частное или произведение выводится корректно.

Если перемножать (складывать и т.д.) показатели, которые являются функциями от разных дат, то результат в отчет не выводится.

СУММА, КОЛИЧЕСТВО и другие «жадные» функции

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

По умолчанию формула может быть вычислена только в том случае, если у каждого аргумента формулы есть определенное единственное значение (или задано значение по умолчанию).

Но если аргумент стоит внутри «жадной» функции, то функция будет вычислена и в том случае, если у аргумента много значений. Поэтому функция и называется «жадной» - забирает все возможные значения.

В простейшем виде формула имеет вид:

Суммарное производство = СУММА (Производство)

Число производителей = КОЛИЧЕСТВО (Производитель)

Пример формулы с фильтрами:

Обработано с начала года ПРИ (Период = Текущий квартал) = СУММА (Обработано ПРИ (Период <= в текущем году Текущий квартал))

В одной формуле можно использовать несколько «жадных» функций:

Средний надой = СУММА (Надой) / КОЛИЧЕСТВО (Надой)

Для суммирования вычисленных значений нужно понимать, по какому принципу объединять аргументы. По умолчанию при расчете объединяются аргументы, которые получены из одного объекта (подробнее об объектах см. раздел Редактор объектов). Но есть возможность просуммировать по множеству, дереву или периоду, явно указав показатель типа «Справочник», «Древовидный справочник» или «Период», по которому суммируются значения. Например:

Сумма v3 = СУММА (v3, показатель, по которому суммируем)

v3 = v1 / v2

Список «жадных» функций (в формате «функция (количество обязательных аргументов) - описание»):

  • КОЛИЧЕСТВО_УНИК (1) - Количество уникальных значений.
  • МЕДИАНА (1) - Значение, которое делит ранжированную совокупность значений показателя на две равные части.
  • СТАНДАРТОЕ_ОТКЛОНЕНИЕ (1) - Мера разброса (отклонения от среднего) значений показателя. Квадратный корень из дисперсии.
  • КОЛИЧЕСТВО (1) - Количество значений показателя. Для вычисления количества значений двух и более показателей используйте «+» (плюс).
  • СРЕДНЕЕ (1) - Среднее значений показателя.
  • СУММА_ЗПТ_УНИК (1) - Получение строки, в которой запятыми разделены уникальные значения показателя.
  • МИН (1) - Минимум значений показателя.
  • ДИСПЕРСИЯ (1) - Мера разброса (отклонения от среднего) значений показателя.
  • УНИК_СТР (2) - Объединение уникальных значений показателя, задаваемого первым аргументом, в строку с разделителем, задаваемым вторым аргументом.
  • ЕСТЬ_ЗНАЧЕНИЕ (1) - Определение уникального значения (спасает от расслоения).
  • СУММА_ЗПТ (1) - Получение строки, в которой запятыми разделены значения показателя.
  • МАКС (1) - Максимум значений показателя.
  • КВАНТИЛЬ (2) - Значение, меньше которого заданная вторым аргументом часть значений показателя, заданного первым аргументом.
  • СУММА (1) - Сумма значений показателя. Для суммирования двух и более чисел или показателей используйте «+» (плюс).
  • ПОСЛЕДНЕЕ (1) - Последнее из значений первого аргумента, отсортированных по признакам для этого аргумента, заданных дополнительными аргументами или фильтрами на первый аргумент.

Расчет суммы и количества значений для нескольких показателей

Расчет количества значений для двух показателей:

КОЛИЧЕСТВО ( Показатель1 ) + КОЛИЧЕСТВО ( Показатель2 )

Расчет суммы значений для двух показателей:

СУММА ( Показатель1 ) + СУММА ( Показатель2 )

Функция ПОСЛЕДНЕЕ

Формула

А = ПОСЛЕДНЕЕ (В, Период)

возвращает значение показателя B с наибольшим значением признака Период.

Формула

А ПРИ Период = Х = ПОСЛЕДНЕЕ (А ПРИ Период.Родительский элемент в дереве = Х)

выражает расчет агрегации значений показателя A с функцией агрегации «последнее по периоду».

«Жадные» макросы

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

Список «жадных» макросов - аналогов «жадных» функций, но которые автоматически агрегируют и производят вложенные вычисления по аналитическим признакам показателя - первого аргумента (или по аргументам со второго, если заданы):

  • А_СУММА.
  • А_МЕДИАНА.
  • А_СТАНДАРТОЕ_ОТКЛОНЕНИЕ.
  • А_СРЕДНЕЕ - агрегирует «правильно», т.е. считает по уровням не среднее средних, а настоящее среднее.
  • А_ДИСПЕРСИЯ.
  • А_УНИК_СТР.
  • А_МИН.
  • А_МАКС.
  • А_КВАНТИЛЬ.
  • А_КОЛИЧЕСТВО_УНИК.

Другие жадные макросы:

  • СУММА_ЗА (3) - Сумма значений первого показателя-аргумента за несколько предыдущих значений второго показателя-аргумента. Третий аргумент задает число предыдущих значений.
  • ПРОЦЕНТ_ОТ (2) - Процент, который составляют значения первого аргумента, по второму аргументу. Например «ПРОЦЕНТ_ОТ ( прибыль, магазин )».
  • A_ПРОЦЕНТ_ОТ - автоматически агрегирующий аналог ПРОЦЕНТ_ОТ.

Функции прогнозирования

Доступны с релиза 1.40.0.

  • SARIMA (8, 9) - Прогноз по сезонной интегрированной модели авторегрессии - скользящего среднего. Принимает восемь или девять аргументов. Первый аргумент - показатель (или вложенное выражение), значения которого определяют параметры модели для построения прогноза (т.е. значения, которые и будут «продолжены» в будущее результатом вычисления прогноза). Следующие аргументы - целые числа, определяют порядки модели: порядок авторегресси (d), порядок интегрирования (d), порядок скользящего среднего (q), порядок авторегресси сезонной составляющей (P), порядок интегрирования сезонной составляющей (D), порядок скользящего среднего сезонной составляющй (Q), сезонность временного ряда (s), например s = 12 для данных по месяцам и сезонности равной 1 году. Последний (необязательный) аргумент - показатель типа «Период», задает временной признак для прогноза, определяющий динамику изменения значений во времени и моменты времени для прогноза из фильтров. Если последний аргумент не задан, то он автоматически определяется по аналитическим признакам показателя - первого аргумента. Предполагается, что параметры подбираются в сторонней системе.
  • ПРОГНОЗ (1, 2) - Автоматический прогноз по обобщенной аддитивной модели y(t) = g(t) + s(t) + h(t) + Et, где g(t) представляет тренд, s(t) - сезонность, h(t) - аномальные дни, Et - ошибку. Принимает один или два аргумента. Первый аргумент - показатель (или вложенное выражение), значения которого определяют параметры модели для построения прогноза (т.е. значения, которые и будут «продолжены» в будущее результатом вычисления прогноза). Второй (необязательный) аргумент - показатель типа «Период», задает временной признак для прогноза, определяющий динамику изменения значений во времени и моменты времени для прогноза из фильтров. Если второй аргумент не задан, то он автоматически определяется по аналитическим признакам показателя - первого. Подробней о методе прогноза: https://research.fb.com/prophet-forecasting-at-scale/, https://peerj.com/preprints/3190.pdf
  • ПРОГНОЗ_ВЕРХ (1, 2) - аналогична ПРОГНОЗ, но считает верхнюю оценку прогноза.
  • ПРОГНОЗ_НИЗ (1, 2) - аналогична ПРОГНОЗ, но считает нижнюю оценку прогноза.

Примеры применения формул для решения прикладных задач

Расчет количества объектов, отфильтрованных по заданному справочнику

Все заявки регистрируются в справочнике-реестре «Заявки», содержащем поля «Название» (уникальный номер заявки), «Вид закупки» (выбирается из справочника «Виды закупок»).

_images/application1.png

Справочник-реестр «Основная таблица процедур» содержит сведения о заявках, участвующих в основных закупочных процедурах, включая данные из справочника «Заявки»: поля «Заявки.Название», «Заявки.Вид закупки».

_images/application2.png

Нужно вычислить общее количество заявок по тем видам закупок, данные о которых включены в справочник «Основная таблица процедур».

  1. Зададим для показателей «Заявка», «Основная таблица процедур» опцию «Автоматически ссылаться на элементы справочника» (см. описание опции)
_images/application3.png _images/application4.png
  1. Создадим показатель «Количество заявок по виду закупок» с формулой:

Количество заявок по виду закупок ПРИ Вид закупки = X = ЕСЛИ (ЕСТЬ_ЗНАЧЕНИЕ ( Основная таблица процедур ПРИ Заявка.Вид закупки = X ), КОЛИЧЕСТВО ( Заявка ПРИ Вид закупки = X ))

_images/application5.png

Данный показатель вычисляет количество заявок по определенному виду закупок, включенному в справочник «Основная таблица процедур». Для вида закупок, не включенного в справочник «Основная таблица процедур», количество заявок не рассчитывается.

Проверим расчет с помощью отчета:

_images/application6.png
  1. Создадим показатель «Общее количество заявок по видам закупок, включенным в Основную таблицу процедур» с формулой:

Общее количество заявок по видам закупок, включенным в Основную таблицу процедур = СУММА ( Количество заявок по виду закупок , Вид закупки )

_images/application7.png

Данный показатель вычисляет общее (суммарное) количество заявок по всем видам закупок, включенным в справочник «Основная таблица процедур».

Проверим расчет с помощью отчета:

_images/application8.png

Расчет статистики за произвольный период на основе данных, привязанных к периоду типа «День»

Справочник-реестр «Заявки» содержит поля «Организация-участник» (выбирается из справочника «Организации-участники») и «Дата создания заявки» («Период» типа «День»).

_images/application10.png

Нужно подсчитать количество организаций-участников, подавших заявки за определенный период (период типа «День», «Месяц» или «Год»).

  1. Зададим для показателя «Заявка» опцию «Автоматически ссылаться на элементы справочника» (см. описание опции)
_images/application11.png
  1. Создадим показатель «Количество участников, подавших заявки» с формулой:

Количество участников, подавших заявки ПРИ Период = X = КОЛИЧЕСТВО_УНИК (ЕСЛИ ((( Заявка.Дата создания заявки >= ПЕРИОД (НАЧАЛО_ПЕРИОДА ( Период (из фильтров) ПРИ Период = X ))) * ( Заявка.Дата создания заявки < ПЕРИОД (НАЧАЛО_ПЕРИОДА (( Период (из фильтров) ПРИ Период = X + 1))))), Заявка.Организация-участник ))

_images/application12.png

Функция КОЛИЧЕСТВО_УНИК вычисляет количество уникальных значений показателя «Заявка.Организация-участник», полученных в результате выполнения функции ЕСЛИ.

Функция ЕСЛИ выбирает только такие значения показателя «Заявка.Организация-участник», которые удовлетворяют условиям отбора, заданным для Заявок.

Условие отбора Заявка.Дата создания заявки >= ПЕРИОД (НАЧАЛО_ПЕРИОДА ( Период (из фильтров) ПРИ Период = X )) проверяет, что у Заявки значение показателя «Дата создания заявки» больше либо равно дате начала Периода X, заданного в левой части формулы.

Условие отбора Заявка.Дата создания заявки < ПЕРИОД (НАЧАЛО_ПЕРИОДА (( Период (из фильтров) ПРИ Период = X + 1))) проверяет, что у Заявки значение показателя «Дата создания заявки» меньше даты начала периода, следующего за Периодом X, заданным в левой части формулы.

Операция * означает, что эти условия отбора должны выполняться одновременно. Таким образом отбираются только те Заявки, у которых значение показателя «Дата создания заявки» попадает в Период X, заданный в левой части формулы .

  1. Проверим расчет с помощью отчета:
_images/application13.png

Просмотрим информацию о вычислении значения «2», рассчитанного для Периода «август 2018 г.»:

_images/application14.png

Кликнув на значение «Организация-участник 14», просмотрим информацию о вычислении этого значения:

_images/application15.png

Автоматическое продолжение значений показателя по периоду

Отчетная форма «Место пребывания управляющего (исполнительного) директора» содержит показатели «Дата начала отсутствия» и «Дата окончания отсутствия» (типа «Дата и время»).

Эта форма заполняется ежедневно, начиная со дня начала отсутствия директора. Дата заполнения формы задается показателем «Период».

Заданные значения сроков отсутствия изменяются редко, поэтому для исключения необходимости постоянного дублирования введенных данных система должна обеспечить автоматическое продолжение заданных значений по периоду до наступления даты окончания отсутствия.

  1. Зададим формулу для расчета «Даты начала отсутствия»:

Дата начала отсутствия ПРИ Период = X = ЕСЛИ ((ПЕРИОД (ПОСЛЕДНЕЕ (Дата окончания отсутствия ПРИ Период < X)) >= Период (из фильтров) ПРИ Период = X), ПОСЛЕДНЕЕ (Дата начала отсутствия ПРИ Период < X))

_images/period_continued_start.png
  1. Зададим формулу для расчета «Даты окончания отсутствия»:

Дата окончания отсутствия ПРИ Период = X = ЕСЛИ ((ПЕРИОД (ПОСЛЕДНЕЕ (Дата окончания отсутствия ПРИ Период < X)) >= Период (из фильтров) ПРИ Период = X), ПОСЛЕДНЕЕ (Дата окончания отсутствия ПРИ Период < X))

_images/period_continued_end.png
  1. Проверим работу формул на примере.

Форма заполнена в день начала отсутствия директора:

_images/period_continued.png

На следующий день в форме автоматически отобразятся заданные значения:

_images/period_continued2.png

Если получены новые сведения, необходимо изменить данные о сроках отсутствия директора:

_images/period_continued3.png

После сохранения измененного значения система будет выводить подсказку о корректировке исходных данных:

_images/period_continued4.png

В последующие дни в форме автоматически будет отображаться измененное значение:

_images/period_continued5.png

Подсветка значений показателей, которые были изменены за последний отчетный период

Существует задача межпериодного контроля, которая требует контролировать изменение значений показателя за период.

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

Рассмотрим конкретный пример.

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

Допустим, значения наблюдаемого показателя привязаны к показателю «Период». Промежуток времени, в течение которого отслеживаются изменения наблюдаемого показателя, задается показателем «Служебный период».

Создаем показатель-индикатор «Изменения бухгалтерского баланса» и задаем для него следующую формулу:

Изменения бухгалтерского баланса ПРИ Служебный период = X = (МАКС (Время изменения объекта) >= НАЧАЛО_ПЕРИОДА (Служебный период (из фильтров) ПРИ Служебный период = X)) * (МАКС (Время изменения объекта) < НАЧАЛО_ПЕРИОДА (Служебный период (из фильтров) ПРИ Служебный период = X+1))

В формуле используется системный показатель «Время изменения объекта» (см. подраздел Системные показатели типа «Дата и время»).

Далее создаем отчет, структура которого отображена на рисунке:

_images/mpk2.png

В заголовках столбцов указаны:

  • Источник данных: наблюдаемый показатель «Значения бухгалтерского баланса».
  • Фильтр по аналитическому признаку «Статьи Финансового Учета [спр]», ограничивающий рассматриваемые значения наблюдаемого показателя.
  • Фильтр по показателю «Период» (параметр), ограничивающий рассматриваемые значения наблюдаемого показателя.
  • Источник данных: показатель-индикатор «Изменение бухгалтерского баланса».
  • Фильтр по показателю «Служебный период» (параметр), ограничивающий рассматриваемые значения показателя-индикатора.

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

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

B* >0 : A*.bgcolor=red

Далее скрываем второй столбец отчета и получаемый необходимый отчет, который отображен на следующем рисунке.

_images/mpk1.png

Использование в отчете (дашборде) параметра с типом «Период» для данных, привязанных к датам

Предположим, есть справочник «Контракты», где указаны номер контракта («Название»), дата заключения контракта (тип показателя: дата) и сумма контракта.

_images/contracts_1.PNG

В отчет (дашборд) нужно вывести количество контрактов, заключенных в отчетном периоде. Отчетный период должен быть параметром отчета (дашборда).

У показателя, который ссылается на справочник «Контракты», выберем опцию «Автоматически ссылаться на элементы справочника = Да».

_images/contracts_2.PNG

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

_images/num_contracts.PNG

Создадим показатель «Отчетный период» типа «Период», вычисляемый на основе показателя «Дата заключения контракта». Функция ПЕРИОД преобразует дату к периоду, содержащему эту дату. Первый аргумент задает дату, второй - тип периода (в нашем примере тип периода - «год»).

Проставим у показателя «Отчетный период» обязательные аналитические признаки, по которым отбираются данные (в нашем примере обязательный аналитический признак - «Контракты»).

_images/per1.PNG

Построим отчет (дашборд), включим в него показатель-источник «Количество контрактов» и фильтр по показателю «Отчетный период», который является параметром.

_images/per_fitr_4.PNG

Выбрав значение параметра «2018 г.», получим отчет:

_images/per_fitr_3.PNG

Использование функций RANK, В_ДОЛЕ

Допустим, в системе собираются данные об объемах продаж 15-ти торговых организаций розничной торговой сети.

_images/rank-1.png

С помощью функции RANK можно ранжировать организации по величине объема продаж:

_images/rank-2.png

Показатель «Ранг организации по объему продаж» вычислен с помощью функции RANK:

_images/rank-3.png

Обязательный аналитический признак «Организация» указывает на показатель, значения которого ранжируются по аргументу функции RANK.

Теперь можно из общего списка организаций выбрать 3 организации с максимальным объемом продаж (с помощью фильтра по условию «Ранг организации по объему продаж <= 3»):

_images/rank-4.png

Аналогичным образом формируется список 3-х организаций с минимальным объемом продаж:

_images/rank-5.png

Допустим, нужно выбрать минимальный перечень организаций с максимальным объемом продаж, суммарный объем продаж которых составляет не менее 50 процентов от общего объема продаж всех организаций торговой сети.

Для решения такой задачи используется функция В_ДОЛЕ. В данном примере создадим расчетный показатель «Доля 60 процентов от общего объема продаж»:

_images/rank-6.png

Проверим результат расчета, добавив показатель «Доля 60 процентов от общего объема продаж» в ежедневный отчет по объему продаж:

_images/rank-7.png

Можно и отдельно вывести список организаций, формирующих 60 процентов объема продаж:

_images/rank-8.png

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