Сегодня я хочу показать метод универсального построения отчетов, вот в таком стиле, как на картинке выше, то есть количество строк (показателей) известно заранее, а вот данные каждого показателя могут рассчитываться по разным принципам.
Когда поможет такой метод? Например, когда в строках отчета необходимо рассчитывать и отображать результат по совершенно разным формулам, да ещё и по совершенно разным источникам в модели даных, где встречаются строки «в том числе» или строки, суммирующие частично другие строки.
Итак, приступим.
Исходные данные:
Итак, рассмотрим модель данных, в которую собраны воедино данные из разных источников. Тут и баланс, и доходы/расходы и разные разрезы (кредиты), прочие формы. Всё это собрано и объединено в одну модель:
А нам требуется составить отчет, в котором вперемешку собраны разные показатели (в справочнике):
Справочник, как мы видим, создается отвязанным от модели (таблица с другими таблицами не соединяется). Главное в справочнике:
- нумерация наименований показателей (необходима для задания порядка вывода строк отчета),
- сами наименования,
- уровень показателя (необходим для определения отступа и раскраски строк отчета).
В справочнике могут быть и другие вспомогательные и информационные колонки.
Вспомогательные переменные:
Для расчета некоторых показателей необходимо не только знать саму дату, на которую надо рассчитать отчет, но ещё и дату на начало периода, относительно которой в отчете рассчитывается динамика (изменение показателя), поэтому введем две переменные:
|
1 2 |
LET vMaxOtcDate = `=Max(ОТЧЕТНАЯ_ДАТА)`; LET vLastOtcKv = `=AddMonths(Max(ОТЧЕТНАЯ_ДАТА),-3,1)`; |
(вторая переменная рассчитывает начало периода, в нашем случае — квартал)
Построение отчета:
Создаем обычную Прямую таблицу («Straight Table»). В качестве её дименшина указываем расчетную формулу:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
=Pick( _ОТЧЕТ_УРОВЕНЬ, ``, ``, ` `) &IF( Match(_ОТЧЕТ_СОРТИРОВКА,12,21) _ОТЧЕТ_ПОКАЗАТЕЛЬ&` на `&Date(vLastOtcKv+1)&`г.` IF( Match(_ОТЧЕТ_СОРТИРОВКА,13,22) _ОТЧЕТ_ПОКАЗАТЕЛЬ&` на `&Date(vMaxOtcDate+1)&`г.` IF( _ОТЧЕТ_УРОВЕНЬ>2 IF( LEN(_ОТЧЕТ_ПОКАЗАТЕЛЬ)>40 MID(_ОТЧЕТ_ПОКАЗАТЕЛЬ,1, 28+Index(MID(_ОТЧЕТ_ПОКАЗАТЕЛЬ,30),` `) ) &chr(10)&` `& MID(_ОТЧЕТ_ПОКАЗАТЕЛЬ,30+Index(MID(_ОТЧЕТ_ПОКАЗАТЕЛЬ,30),` `) ) _ОТЧЕТ_ПОКАЗАТЕЛЬ ) _ОТЧЕТ_ПОКАЗАТЕЛЬ ))) |
Особенности расчетной формулы:
- в начале формулы делаем сдвиг в зависимости от указанного уровня показателя отчета.
- наименования некоторых показателей «достраиваются» налету, исходя из выбранной отчетной даты.
- если длина наименования показателя третьего уровня больше 40 (подбирается в зависимости от ширины колонки в нашем отчете), то делается принудительный перенос по ближайшему пробелу, чтобы самим выровнять вторую (перенесенную) строчку до уровня сдвига первой.
Произвольная формула для каждой строки отчета:
Основная идея построения универсального отчета по показателям — для каждой известной строки отчета мы можем указать свою произвольную формулу.
- Для этого формула выражения на вкладке Выражение (Expressions) должна выглядеть примерно так:
|
1 2 3 4 5 6 7 8 |
Pick( _ОТЧЕТ_СОРТИРОВКА /*1 */ null() /*2 */ Num(Sum( {<КРЕДИТ_ВИД={`ИПОТЕКА`,`ЖИЛЬЕ`}>} КРЕДИТ_ПЕРВОНАЧАЛЬНАЯ_СУММА)/1000, `# ##0`) /*3 */ Num(Sum( {<КРЕДИТ_ВИД={`ИПОТЕКА`}>} КРЕДИТ_ПЕРВОНАЧАЛЬНАЯ_СУММА)/1000, `# ##0`) /*4 */ Count( {<КРЕДИТ_ВИД={`ИПОТЕКА`}>} КРЕДИТ_ПЕРВОНАЧАЛЬНАЯ_СУММА) /*5 */ Num(Sum( {<КРЕДИТ_ВИД={`ИПОТЕКА`}>} КРЕДИТ_ПЕРВОНАЧАЛЬНАЯ_СУММА*КРЕДИТ_СТАВКА/100) /Sum( {<КРЕДИТ_ВИД={`ИПОТЕКА`}>} КРЕДИТ_ПЕРВОНАЧАЛЬНАЯ_СУММА), `# ##0.0%`,`.`) ) |
Как видим, здесь для каждого номера строки (_ОТЧЕТ_СОРТИРОВКА) с помощью функции Pick ():
- прописывается своя произвольная формула,
- приводится к нужным единицам отчета,
- выводится с необходимым форматом.
Таким образом, получается полная универсальность.
Для первых 5 строк отчета мы прописали формулы и получили :
- Продолжаем добавлять строки в наше выражение для каждой строки отчета:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
/*6 */ null() /*7 */ Num(Sum( {<ФОРМА_102_СИМВОЛ={`11114`,`11214`,`11314`, `11115`,`11215`,`11315`}>} ФОРМА_102_ДОХОД_ТЫСЯЧИ), `# ##0`) /*8 */ Num(Sum( {<ФОРМА_102_СИМВОЛ={`11114`,`11214`,`11314`}>} ФОРМА_102_ДОХОД_ТЫСЯЧИ), `# ##0`) /*9 */ Num(Sum( {<ФОРМА_102_СИМВОЛ={`11115`,`11215`,`11315`}>} ФОРМА_102_ДОХОД_ТЫСЯЧИ), `# ##0`) /*10*/ null() /*11*/ null() /*12*/ Num(Sum( {<БАЛ_СЧЕТ={`423*`}, БАЛ_СЧЕТ_ТИП={`П`} БАЛ_СЧЕТ_ВАЛЮТА={`RUB`} ОТЧЕТНАЯ_ДАТА={`@(=Date(vLastOtcKv))`}>} БАЛ_СЧЕТ_ОСТАТОК)/1000, `# ##0`) /*13*/ Num(Sum( {<БАЛ_СЧЕТ={`423*`}, БАЛ_СЧЕТ_ТИП={`П`} БАЛ_СЧЕТ_ВАЛЮТА={`RUB`} ОТЧЕТНАЯ_ДАТА={`@(=Date(vMaxOtcDate))`}>} БАЛ_СЧЕТ_ОСТАТОК)/1000, # ##0 ) /*14*/ Num((Sum( {<БАЛ_СЧЕТ={`423*`}, БАЛ_СЧЕТ_ТИП={`П`}, БАЛ_СЧЕТ_ВАЛЮТА={`RUB`} ОТЧЕТНАЯ_ДАТА={`@(=Date(vMaxOtcDate))`}>} БАЛ_СЧЕТ_ОСТАТОК) -Sum( {<БАЛ_СЧЕТ={`423*`}, БАЛ_СЧЕТ_ТИП={`П`}, БАЛ_СЧЕТ_ВАЛЮТА={`RUB`} ОТЧЕТНАЯ_ДАТА={`@(=Date(vLastOtcKv))`}>} БАЛ_СЧЕТ_ОСТАТОК))/1000, `# ##0`) /*15*/ null() /*16*/ Num(Sum( {<ФОРМА_202_СИМВОЛ={`20`}>} ФОРМА_202_СУММА)/1000, `# ##0`) /*17*/ Num(Sum( {<ФОРМА_202_СИМВОЛ={`94`}>} ФОРМА_202_СУММА)/1000, `# ##0`) /*18*/ null() /*19*/ Num((Sum( {<БАЛ_СЧЕТ={`454*`}, БАЛ_СЧЕТ_ТИП={`А`} ОТЧЕТНАЯ_ДАТА={`@(=Date(vMaxOtcDate))`}>} БАЛ_СЧЕТ_ОСТАТОК) -Sum( {<БАЛ_СЧЕТ={`454*`}, БАЛ_СЧЕТ_ТИП={`А`} ОТЧЕТНАЯ_ДАТА={`@(=Date(vLastOtcKv))`}>} БАЛ_СЧЕТ_ОСТАТОК))/1000, `# ##0`) /*20*/ null() /*21*/ Num(Sum( {<БАЛ_СЧЕТ={`454*`}, БАЛ_СЧЕТ_ТИП={`А`} ОТЧЕТНАЯ_ДАТА={`@(=Date(vLastOtcKv))`}>} БАЛ_СЧЕТ_ОСТАТОК)/1000, `# ##0`) /*22*/ Num(Sum( {<БАЛ_СЧЕТ={`454*`}, БАЛ_СЧЕТ_ТИП={`А`} ОТЧЕТНАЯ_ДАТА={`@(=Date(vMaxOtcDate))`}>} БАЛ_СЧЕТ_ОСТАТОК)/1000, `# ##0`) |
После добавления этих строк получили:
Оформление финансового отчета в QlikView
Поскольку наш справочник отчета содержит дополнительную информацию об уровне показателя, с её помощью можно реализовать и различный фон для наших строк отчета.
- На вкладке Выражения (Expressions):
- Раскроем {+} и для Цвет фона («Background Color») пропишем условие:
|
1 |
Pick(_ОТЧЕТ_УРОВЕНЬ, RGB(72,105,138), RGB(192,192,192) ) |
- Для Цвет текста («Text Color») пропишем формулу:
|
1 |
Pick(_ОТЧЕТ_УРОВЕНЬ, RGB(255,255,255), RGB(0,0,0) ) |
- Для Формат текста («Text Format») пропишем просто константу:
1=`<b>`
- Переключим режим Полное накопление («Total Mode») на Без накопления («No Totals»).
- На вкладке Измерения (Dimensions):
- Раскроем {+} только у нашего дименшина и в Цвет фона («Background Color») пропишем то же самое условие, что и делали выше:
Pick(_ОТЧЕТ_УРОВЕНЬ, RGB(72,105,138), RGB(192,192,192) ) - В Цвет текста («Text Color») аналогично:
Pick(_ОТЧЕТ_УРОВЕНЬ, RGB(255,255,255), RGB(0,0,0) ) - В Формат текста («Text Format») пропишем своё условие:
Pick(_ОТЧЕТ_УРОВЕНЬ,<b>) - Включим галочку «Скрыть, когда значения Null» («Suppress When Value Is Null»).
- На вкладке Представление (Presentation) нам нужно убрать галочку «Скрыть нулевые значения» («Suppress Zero-Values»). Это необходимо, чтобы отобразились все строки отчета, включая те, которые являются заголовками или не имеющие сумм.
Получили следующее:
- Остаемся на вкладке Представление (Presentation):
- Выключаем галочки Разрешить перетаскивание («Allow Drag and Drop»), Индикаторы сортироваки («Sort Indicator») и Индикаторы выбора («Selection Indicators»).
- Очистим галочки для Нулевое значение («Null Symbol») и Пропущенные значения («Missing Symbol»).
- Включим галочку Скрыть строку заголовка («Suppress Header Row»)
- Включим галочку Перенос в ячейках («Wrap Cell Text») и впишем в Высота ячейки («Cell Height») = 3.
- На вкладке Заголовок (Caption) прописываем нужный заголовок отчета и оставляем нужные Специальные значки («Special Icons»).
Вот и получился наш отчет:
Расскажите, какие вы хитрости используете для создания отчетов в QlikView? Как используете функцию pick в разработке?
Василий Петренко
B3
Максим
Stepan
Павел Карасев
Отвечая на вопрос Алексея «Как используете функцию pick в разработке?»: да, очень полезная функция.
У меня самое частое ее использование — в выражениях объектов визуализации (Expressions) в сочетании с Match() как аналог скриптовой функции ApplyMap — заменить одно значение на другое согласно некоторому справочнику/мэппингу.
Синтаксис:
Pick( Match( ЗначениеДляЗамены, МассивЗначенийДляЗамены ), МассивЗначенийНаКоторыеЗаменить )
Реальный пример использования — при включении синтетических измерениях (synthetic dimensions) в чартах, когда в выражении объекта визуализации (expression) нужно указать разные формулы в зависимости от контекста — значения синт.измерения для данной ячейки.
Например, как и у Алексея, таблица с тремя разными KPI:
1) синт.измерение: =ValueList(‘KPI1’, ‘KPI2’, ‘KPI3’)
2) выражение:
=Pick(Match(
//Копия синтетического измерения
ValueList(‘KPI1’, ‘KPI2’, ‘KPI3’),
//Копия параметра синтетического измерения
‘KPI1’, ‘KPI2’, ‘KPI3’
),
//Формулы расчета для каждого элемента синт.измерения
Sum(KPI1), Sum(KPI2), Sum(KPI3)
)
Про более сложный случай применения — построение гистограммы с разбиением диапазона значений на ряд последовательных интервалов без пропусков (чем страдает Class(Aggr())) — напишу как нибудь отдельную статью.