Вычисляемые показатели

Интерфейс ввода формул

Формулы для вычисления показателя задаются в окне редактирования или создания показателя (в паспорте показателя или в АП/конструкторе: иконка шестеренки справа от названия показателя или кнопка “Новый показатель”).

_images/115.png

По нажатию на вкладку «Формула» разворачивается область для задания формул.

_images/116.png

В этой области есть:

  • Кнопка «Выбрать аргументы» — для выбора показателей, которые будут использоваться в формулах.
  • Список показателей, выбранных для использования в формуле (под кнопкой «Выбрать аргументы»).
  • Набор кнопок с математическими знаками («+», «-», «*», «/», «(», «)», «=») для использования в формулах.
  • Кнопки с выпадающим списком доступных в Cистеме формул.
  • Поле для ввода формул.

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

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

Все выбранные показатели появятся в списке под кнопкой “Выбрать аргументы”. Каждому из них будет присвоена буквенная переменная (А, В, С и так далее), актуальная только для задания формулы в этом показателе. Она указана в поле с показателем (слева от его названия).

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

При вводе формул обязательно, чтобы в левой части формулы в качестве аргумента стоял показатель, который в настоящий момент редактируется (невозможно при редактировании показателя А написать формулу вида В=С+Е).

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

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

Задание формулы

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

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

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

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

Фильтры в формулах

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

_images/118.png

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

_images/119.png

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

_images/120.png

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

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

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

Вычисление формул

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

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

Допустим, есть показатель Наценка, для которого задана формула Наценка = Цена продажи - Цена закупки. При этом для товара Apple iPhone 5 32Gb black : Цена продажи = 1280, а Цена закупки — не определена (нет загруженных или введенных значений). Тогда Наценка для выбранного товара не может быть вычислена. И в отчете с источником Наценка и фильтром Товар = Apple iPhone 5 32Gb black будет показана пустая ячейка.

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

Допустим, есть показатель Расходы = Зарплата + Налоги. При этом за 2.06.2014 было выдано зарплаты на 13000 и уплачено налогов на 26000, и в другие дни июня ни зарплата, ни налоги не платились. Если необходимо вычислить Расходы за июнь 2014 года, то при указанной выше формуле значение показателя Расходы для периода = июнь 2014 г. вычислено не будет, поскольку в Системе не существует значений показателей Зарплата и Налоги за июнь, а есть только за конкретные дни.

Чтобы значение показателя Расходы было вычислено в таком случае, необходимо писать формулу показателя Расходы = СУММА (Зарплата) + СУММА*(*Налоги). Тогда Система сначала посчитает суммы показателей Зарплата и Налоги за июнь, а затем, через них, посчитает значение показателя Расходы.

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

_images/117.png

Правила написания формул

Общее

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

Для создания или изменения формулы расчета показателя в карточке показателя предусмотрено окно редактирования формул (подробнее см. в подразделе «Задание формул и агрегации»).

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

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

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

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

  • Подставить значение одного аналитического признака в другой признак:
f(p=x) = g(k=x) + h(p=x)
  • Проигнорировать значение аналитического признака внутри формулы:
f(p=x) = g() + h(p=x)
  • Заменить значение аналитического признака на фиксированное значение:
f(p=x) = g(p= "foo") + h(p=x)

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

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

_images/121.png

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

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

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

Голы команды ПРИ Команда=x = Голы гостя ПРИ Гость=x + Голы хозяина ПРИ Хозяин=x

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

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

_images/122.png

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

Подробнее о порядке элементов справочника см. в подразделе «Порядковый номер».

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

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

_images/196.png

Применять только аналитические признаки

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

_images/402.png

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

Например, если используется:

  • Показатель 1, с признаками X и Y,
  • Показатель 2, с признаками X и Y,
  • Показатель 3, с признаком X.

и задана формула для Показателя 1:

_images/403.png

То если вывести в отчете Показатель 1 (с заданными в отчете признаками X=x1, Y=y1), Система попробует обработать формулу:

Показатель 1 = Показатель 2 ПРИ (X=x1 И Y=y1) + Показатель 3 ПРИ (X=x1 И Y=y1). Но значения для Показателя 3 (с признаками X=x1, Y=y1) не существует, т.к. у Показателя 3 признака Y нет. Формула не посчитается.

Если же в формуле отметить для показателя Показатель 3 чекбокс «Применять только аналитические признаки», то, соответственно, при выводе в отчете показателя Показатель 1 (с признаками X=x1, Y=y1) - Система обработает формулу:

Показатель 1 = Показатель 2 ПРИ (X=x1 И Y=y1) + Показатель 3 ПРИ (X=x1). Обработка пройдет корректно и формула посчитается.

Таким образом, эта формула практически эквивалентна формуле со свободной переменной:

Показатель 1 ПРИ (Y=y) = Показатель 2 ПРИ (Y=y) + Показатель 3, за исключением того, что не требует существования фильтра по Y на Показатель 1 для расчета.

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

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

_images/197.png

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

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

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

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

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

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

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

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

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

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

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

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

Агрегация

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

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

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

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

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

Показатель1 = Показатель2.Показатель1 , заданная для Показатель1

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

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

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

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

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

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

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

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

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

Период1 + 2

Период1 - 1

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

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

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

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

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

Функции

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

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

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

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

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

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

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

ЕСЛИ ( Признак = 10, Значение, 0 )

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

_images/396.png

где показатель «Значение_ЕСЛИ» вычисляется по формуле с использованием функции «ЕСЛИ»:

_images/397.png

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

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

Функция ЕСТЬ

Функция ЕСТЬ - выражение-аргумент этой функции имеет одно значение, «1» - если значение есть, «пусто» - если значения нет.

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

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

_images/398.png

Показатель «Значение_ЕСТЬ» вычисляется по формуле с использованием функции «ЕСТЬ»:

_images/399.png

Функцию «ЕСТЬ» можно использовать внутри других функций, например, в функции «ЕСЛИ»:

ЕСЛИ ( ЕСТЬ Значение , 5, 0)

_images/400.png

Также у функции существует «жадный» вариант - функция «ЕСТЬ_ЗНАЧЕНИЕ». Эта функция определяет уникальное значение («есть хотя бы одно значение»).

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

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

Период_день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. Формула

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

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

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

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

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

«Жадные» функции

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

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

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

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

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

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

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

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

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

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

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

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

A = B / C

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

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

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

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

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

СУММА ( Показатель1 ) + СУММА ( Показатель2 )
Функция ПОСЛЕДНЕЕ

Формула, возвращающая значение показателя B с наибольшим значением признака Период:

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

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

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

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

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

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

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

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