Вычисления

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

Функции в отчетах (агрегация)

Настройка функций источников данных прямо в отчетах возможна только для показателей, у которых не настроены формулы их вычисления через другие показатели. У таких показателей при переходе к настройкам источника данных (по символу “сигма” (Σ)) будет активным выпадающий список “(fx)”.

_images/math_01.png

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

_images/math_02.png

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

Допустим, в системе есть следующие данные о продажах телефона Apple iPhone 5 32Gb black за 1 сентября 2013 года: в магазине Капитолий было продано 2 штуки, в магазине ГУМ - 2 штуки, ИМ - 1 шт, Омега Плаза - 1шт, СВ2-Тверская - 3шт. И задана структура отчета, где однозначно определена дата (1.09.2013) и товар (Apple iPhone 5 32Gb black). В качестве единственного источника данных выбран показатель “Штуки”.

Вот как повлияют на полученные в отчете данные различные доступные функции агрегации:

  • “СУММА” — в ячейке выводится сумма всех содержащихся в системе данных, удовлетворяющих всем указанным признакам.

То есть в отчете в ячейке будет выведено (2+2+1+1+3) = 9.

_images/math_03.png

Аналогично происходит и суммирование (и действия с другими функциями) по периоду - если существуют данные по продажам того же телефона, в конкретном магазине за 1, 2, 7, 10, 12, 13 и 29 сентября 2013 года (а в остальные дни он не продавался), то в отчете, где фильтр “период” задан как “сентябрь 2013 г.”, будет выведена сумма данных по продажам за эти дни (если в качестве функции источника указана сумма).

  • “КОЛИЧЕСТВО” — в ячейке выводится количество записей в системе, удовлетворяющих всем указанным признакам.

В результате в ячейке будет выведено число магазинов, в которых в этот день продавался данный товар, поскольку записи о продажах этого товара есть только в магазинах, где его продали. (1+1+1+1+1) = 5.

_images/math_05.png
  • “МИНИМУМ” — в ячейке выводится минимальное значение из всех содержащихся в системе данных, удовлетворяющих всем указанным признакам.

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

_images/math_07.png
  • “МАКСИМУМ” — в ячейке выводится максимальное значение из всех содержащихся в системе данных, удовлетворяющих всем указанным признакам:

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

_images/math_09.png
  • “СРЕДНЕЕ” — в ячейке выводится среднее арифметическое значение для всех содержащихся в системе данных, удовлетворяющих всем указанным признакам.

Показатель “Штуки” с функцией СРЕДНЕЕ в рассматриваемом примере будет вычислен как сумма всех данных, удовлетворяющих фильтрам отчета (9) разделенная на количество таких записей (5). Результат, который будет выведен в ячейке зависит от того, показателем какого типа является “Штуки”. Если это число, то выведется 1,8. Если же “Штуки” — это целое (как в данном примере), то будет выведен результат, округленный до ближайшего целого числа по стандартным правилам округления. То есть, 2.

_images/math_11.png
  • “(без формулы)” — в ячейке выводится значение, удовлетворяющее фильтрам отчета. Если таких значений больше, чем одно — ячейка раскрашивается в оранжевый цвет и выводится информация о расслоении данных.

То есть, в рассматриваемом примере, если у показателя “Штуки” выбран вариант отображения “(без формулы), получится расслоение данных (ячейка будет окрашена в оранжевый цвет). Это произойдет потому, что выбранным фильтрам (день+товар) и источнику данных удовлетворяют пять записей из пяти разных магазинов.

_images/math_13.png

Если же добавить в отчет еще один фильтр и указать конкретный магазин, например “ИМ”, то в ячейку будут выведены данные по продажам конкретного телефона в конкретном магазине в конкретный день. То есть 1.

_images/math_14.png

Важно обратить внимание, что если для показателя “Штуки” была выбрана функция “(без формулы)” и для него в системе есть данные за 1 сентября, 3 сентября и так далее, но нет данных введенных (загруженных) за период “сентябрь”, то в отчете, где в качестве значения фильтра “Период” будет указан месяц “сентябрь 2013 г.” будет пустая ячейка. Если же, при том же наборе данных, у показателя “Штуки” была выбрана другая функция (например - “Сумма”), то в качестве значения за период “сентябрь” будет выведена сумма всех значений данного показателя для всех дней в сентябре.

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

_images/math_15.png

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

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

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

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

Задаются такие формулы в окне редактирования или создания показателя (иконка шестеренки справа от названи показателя или кнопка “Добавить показатель”).

_images/math_16.png

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

_images/math_17.png

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

  • кнопка “Выбрать аргументы” — для выбора показателей, которые будут использоваться в формулах
  • список показателей, выбранных для использования в формуле (под кнопкой “Выбрать аргументы”)
  • набор кнопок с математическими знаками (“+”, “-”, “*”, “/”, “(”, ”)”, “=”) для использования в формулах
  • кнопка “fx” с выпадающим списком доступных в системе формул
  • поле для ввода формул

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

_images/math_23.png

Обработка множественных значений

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

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

Для того, чтобы указать функцию обработки множественных значений, нужно кликнуть на аргумент в формуле

_images/math_18.png

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

_images/math_19.png

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

Например, для того, чтобы посчитать сумму продаж за три последних периода (от фильтра-периода, указанного в отчете), потребуется создать показатель, вычисляемый по формуле. Внешняя функция СУММА_ЗА предназначена для вычисления чего-либо за последние несколько периодов. Во втором аргументе указывается период, в третьем — количество периодов. А в первом аргументе сказано, что именно считать.

_images/math_20.png

Первый аргумент в данном примере составной. Во-первых, в нем считается СУММА от показателя Цена. В этом случае за указанный период выведется сумма цен. Во-вторых, у показателя Цена стоит функция обработки множественных значений. В ней указано, что если значений несколько, нужно вычислять их сумму.

_images/math_21.png

Записи о продажах в данном примере хранятся по дням. Функция СУММА в первом аргументе суммирует цену продажи по дням за указанный период. А функция обработки множественных значений нужна на тот случай, когда для заданных в отчете признаков на один день приходится несколько записей о продажах.

Доступные функции

В системе, по кнопке “fx”, доступны следующие функции (при наведении на каждую из них в выпадающем списке появляется подсказка, по нажатию на нее она добавляется в окно ввода формул):

  • ЕСЛИ — считает значение в зависимости от условия. В качестве первого аргумента принимается логическое выражение. Если оно истинно, то функция возвращает второй аргумент. Если ложно — то третий. Третий аргумент не обязателен.

    Допустим, требуется выводить сумму продаж только если она больше, скажем, 5. А в остальных случаях выводить 0. Значит, нужна формула “Если сумма цен больше, чем 5 — выводить сумму цен; иначе — 0”. Такая формула будет выглядеть, как показано на картинке ниже.

_images/math_22.png
  • МАКС — выбирает максимальное значение из всех значений показателя для указанных в отчете фильтров
  • МАКС_ИЗ — может принимать несколько аргументов. Возвращает максимальное значение из указанных аргументов. Считается, даже если не все аргументы определены.
  • МИН — выбирает минимальное значение из всех значений показателя для указанных в отчете фильтров
  • МИН_ИЗ — может принимать несколько аргументов. Возвращает минимальное значение из указанных аргументов. Считается, даже если не все аргументы определены.
  • ОКРУГЛ — возвращает ближайшее целое к аргументу (округляет как вверх так и вниз)
  • ЦЕЛОЕ — преобразовывает аргумент к целому (округляет вниз до целого)
  • ПРОЦЕНТ_ОТ — обязательно указывать два аргумента через ”,”. Считает процент, который составляет значение первого аргумента по второму аргументу для указанных в отчете фильтров. Второй аргумент должен быть типа “Справочник” или “Период”

Функция “ПРОЦЕНТ_ОТ” показывает долю первого аргумента при каждом значении второго аргумента от его суммы по второму аргументу в процентах. Например, если для показателя “Доля продаж в магазине” написать формулу:

А = ПРОЦЕНТ_ОТ (B, С)

где В - Объема продаж, а С - Магазины, то формула вычислит значение для А (Доли продаж в магазине) в каждом из магазинов (заданных, например, в заголовках строк) относительно общей суммы объема продаж по всем магазинам (см. ф-цию “СУММА”). И умножит это значение на 100, чтобы перевести в проценты. Для первого магазина получится следующее: Объем продаж в первом магазине / Объем продаж во всех магазинах * 100. Если фильтрами не указать конкретные значения второго аргумента, то функция не сработает и в ячейке будет пусто.

  • СКОЛЬЗЯЩЕЕ_СРЕДНЕЕ — считает среднее арифметическое первого аргумента за несколько предыдущих значений второго аргумента. Второй аргумент должен являться показателем типа “Период”. В качестве третьего аргумента вводится целое число (количество предыдущих значений). В фильтре отчета необходимо поставить такой же показатель, как и во втором аргументе функции, с выбранным значением — это будет точкой отсчета (месяц, от которого отсчитывать предыдущие месяцы, год, от которого отсчитывать предыдущие годы и т.п.)
  • СРЕДНЕЕ — считает среднее арифметическое значение для показателя при указанных в отчете фильтрах
  • СУММА — считает сумму значений показателя при указанных в отчете фильтрах

Функция СУММА агрегирует значение показателя по всем его аналитическим признакам. В случае, если значение аналитического признака не указано в отчете, показатель будет рассчитан для всех существующий значений этого признака и просуммирован. Так же включает автоматическую агрегацию по древовидным справочникам.

Примечание

Значение функции СУММА зависит от выбранных аналитических признаков аргумента.

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

А = СУММА (B, С)

где А — Сумма продаж магазинов, В — Объем продаж, а С — Магазины, то формула будет вычислять суммарное значение показателя Объем продаж по всем магазинам. Т.е. объем продаж в первом магазине + объем продаж во втором магазине + объем продаж в третьем и так далее по всем магазинам. Задание порядка агрегации будет иметь смысл, если показатель B - вычисляемый.

  • СУММА_ЗА — считает сумму значений первого аргумента за несколько предыдущих значений второго аргумента. Второй аргумент должен являться показателем типа “Период”. В качестве третьего аргумента вводится целое число (количество предыдущих значений). В фильтре отчета необходимо поставить такой же показатель, как и во втором аргументе функции, с выбранным значением — это будет точкой отсчета (месяц, от которого отсчитывать предыдущие месяцы, год, от которого отсчитывать предыдущие годы и т.п.)

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

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

Все внесенные изменения необходимо сохранить.

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

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

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

_images/math_24.png

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

_images/math_25.png

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

_images/math_26.png

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

Например, для создания вычисляемого показателя “Отношение суммы продаж в доставке к сумме продаж в самовывозе” потребуется формула, в числителе которой применяется фильтр Магазин.Тип=доставка, а в знаменателе Магазин.Тип=самовывоз.
_images/math_27.png

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

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

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

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

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

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

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

_images/math_28.png

В этом окне требуется перенести нужный аналитический признак в фильтр, поставить галочку в чек-боксе “переменная” и ввести букву переменной, например X.

_images/math_29.png

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

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

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

Голы команды (Команда:X) = Голы гостя (Гость:X) + Голы хозяина (Хозяин:X)

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

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

_images/math_30.png