Сегодня я хочу показать, как с помощью обычного «Bar Chart», или вертикальной гистограммы, реализовать сравнение двух наборов данных по каким-то показателям симметрично относительно центра. Получится так, как на картинке выше, да и к тому же можно будет каждую ветку отображать ещё и по третьему критерию (в данном примере — по годам).
Разберем по шагам.
Исходные данные:
Для данного примера нам нужна простая таблица со значениями показателей по организациям и годам:
1 2 3 4 5 |
POK_KO, POK_YEAR, POK_NAME, POK_VALUE, `0928`, 2012, `Коэффициент долгосрочной ликвидности (Н4)`,84.1, `0928`, 2012, `Коэффициент мгновенной ликвидности (Н2)`,65.1, `1340`, 2013, `Коэффициент текущей ликвидности (Н3)`,73.1, `3249`, 2013, `Норматив достаточности капитала, %`,15.1, |
Показатели {POK_NAME} нам нужно сравнивать между двумя любыми {POK_KO}
Вспомогательные переменные:
Введем ряд констант.
- Ширина, отведенная на центральную область, в которой будут выводиться наименования показателей:
1 |
LET vCenter = 0.8; |
Это доля (80%) от ширины правой части, где рисуются прямоугольники самих значений. Правая и левая части, конечно, равны.
- Следующие две переменные определяют среди всех попадающих в текущий фильтр организаций — минимальную и максимальную. Именно эти две организации будут сравниваться между собой на графике
1 2 3 |
LET vMinKO = `=MinString(POK_KO)`; LET vMaxKO = `=MaxString(POK_KO)`; |
При таком выборе кандидаты на сравнение будут даже при очищенном фильтре.
- Ещё две вспомогательные переменные нужны для вычисления максимума от суммарной отображаемой величины среди всех текущих показателей. Отдельно – для левой части, отдельно – для правой:
1 2 3 4 5 6 7 8 |
LET vAllSum_by_minKO = REPLACE(`=MAX( AGGR( SUM( {<POK_KO={``#(=vMinKO)``}>} IF(Match(POK_YEAR,#(=Min(POK_YEAR)),#(=Max(POK_YEAR))), POK_VALUE)), POK_NAME))`, `#`,`$`); LET vAllSum_by_maxKO = REPLACE(`=MAX( AGGR( SUM( {<POK_KO={``#(=vMaxKO)``}>} IF(Match(POK_YEAR,#(=Min(POK_YEAR)),#(=Max(POK_YEAR))), POK_VALUE)), POK_NAME))`, `#`,`$`); |
Эти переменные нужны для понимания масштаба и вычисления на его основе.
Инструкция по созданию «эффекта бабочки» в QlikView
- Создаем обычный «Bar Chart», где в качестве дименшина указываем поле
POK_NAME - На вкладке Выражения («Expressions») создадим 6 выражений.
Зачем так много?
Мы хотим для каждого показателя одной организации выводится сразу два значения: за максимальный выбранный год и за минимальный, чтобы анализировать их одновременно.
Для этого создаем два отдельных выражения для левой стороны, два для правой.
Одно выражение – центр, с наименованием показателей.
И еще одно выражение – крайнее левое, которое задает сдвиг всем левым значениям, что и создает эффект отображения графика относительно центра.
Итого, шесть выражений:
- Первое выражение задает начальный сдвиг каждому показателю левой половины:
123=RangeMax(vAllSum_by_minKO,vAllSum_by_maxKO)SUM({<POK_KO={`@(=vMinKO)`}>}~~~~~~~~IF(Match(POK_YEAR,@(=Min(POK_YEAR)),@(=Max(POK_YEAR)))POK_VALUE))
В Метка («Label») для этого выражения — пропишем » «, т.е. в кавычках идет пробел. - Второе выражение вычисляет значение для «левой» организации и для «max года»:
1SUM({<POK_KO={`@(=vMinKO)`}>} IF(POK_YEAR=@(=Max(POK_YEAR)) POK_VALUE))
В Метка («Label») для этого выражения пропишем формулу:
1=`значение за @(=Max(POK_YEAR)) год` - Третье выражение вычисляет значение для «левой» организации и для «min года»:
1SUM({<POK_KO={`@(=vMinKO)`}>} IF(POK_YEAR=@(=Min(POK_YEAR))POK_VALUE))
В Метка («Label») для этого выражения пропишем формулу:
1=`значение за @(=Min(POK_YEAR)) год` - Четвертое выражение задает центральную область для надписей — наименований показателей:
1=RangeMax(vAllSum_by_minKO,vAllSum_by_maxKO)*vCenter
В Метка («Label») для этого выражения пропишем тоже » «, т.е. в кавычках идет пробел. - Пятое выражение вычисляет значение для «правой» организации и для «min года»:
1SUM({<POK_KO={`@(=vMaxKO)`}>} IF(POK_YEAR=@(=Min(POK_YEAR)) POK_VALUE))
В Метка («Label») для этого выражения пропишем формулу:
=значение за @(=Min(POK_YEAR)) год
- Шестое выражение вычисляет значение для «правой» организации и для «max года»:
1SUM({<POK_KO={`@(=vMaxKO)`}>} IF(POK_YEAR=@(=Max(POK_YEAR)) POK_VALUE))
В Метка («Label») для этого выражения пропишем формулу:
1 |
=`значение за @(=Max(POK_YEAR)) год` |
Итак, получили такую визуализацию:
- Теперь работаем с визуальными настройками:a) На вкладке Общие (General) прописываем формулу для заголовка о том, что с чем сравнивается.
Для этого оставляем галочку «Показывать заголовок диаграммы» («Show Title in Chart») и вписываем формулу:
123=`кредитная организация (` &vMinKO& `) VS `&,`кредитная организация (` &vMaxKO& `)`
b) На вкладке Стиль (Style):
- Разворачиваем ориентацию прямоугольников горизонтально,
- Cтиль группировки выбираем Стопкой («Staked»).
c) На вкладке Измерения (Dimensions):
- Вписываем в Метка («Label») нашего дименшина слово: «показатель»,
- Отключаем галочки Метка («Label») и Показывать легенду («Show Legend»)
- Включаем галочку «Скрывать, когда значение Null» («Suppress When Value Is Null»).
d) На вкладке Оси (Axes):
- Для самого первого выражения включим галочку «Скрыть ось» («Hide Axis»)
- Теперь зададим общий масштаб по ширнине. Для этого в первом выражении включим галочку «Стат. макс.» («Static Max») и введем следующую формулу:
1=RangeMax(vAllSum_by_minKO,vAllSum_by_maxKO)*(2+vCenter)
Уже получается что-то более похожее на целевой вид:
- Поработаем над распределением значений на нашей гистограмме-бабочке. На вкладке Представление (Presentation):
- Выключаем галочку Показать легенду («Show Legend»),
- Значение параметра Кластерное («Cluster Distance») уменьшим до 2.
- Отключим заголовок и границы
- Теперь изменим раскраску прямоугольников, чтобы она стала интуитивно понятной:
a) На вкладке Выражения («Expressions»).
- Раскроем {+} у первого выражения и в Цвет фона («Background Color») впишем прозрачный цвет:
ARGB(0,255,255,255) - Аналогично в Цвет фона («Background Color») второго выражения впишем:
color(1) - Цвет фона («Background Color») третьего выражения:
color(2) - Цвет фона («Background Color») четвертого выражения снова:
ARGB(0,255,255,255) - Цвет фона («Background Color») пятого выражения:
color(2) - Цвет фона («Background Color») шестого выражения:
color(1)
Первый и второй цвет выбираем и прописываем на свой вкус.
- Для аналитика нужно иметь точные цифры под рукой, поэтому настроим надписи значений внутри квадратиков:
a) На вкладке Представление («Presentation») включаем галочку «Значения внутри сегментов» («Plot Values Inside Segments»),
b) На вкладке Выражения («Expressions») у выражений со второго по шестой включим галочку Значения на точках графика («Values on Data Points»).
с) Если цифры внутри квадратиков надо выводить белым, тогда у выражений 2, 3, 5, 6 (кроме первого и четвертого) надо раскрыть {+} и вписать всем в Цвет текста («Text Color») цвет:
RGB(255,255,255)
d) В центральном квадратике, как мы видим, отображается пока тоже число, а не название показателя. Поменяем это:
- Обернем эту формулу функцией DUAL:
=DUAL( POK_NAME, RangeMax(vAllSum_by_minKO,vAllSum_by_maxKO)*vCenter) - Но и на этом ещё не всё. Дело в том, что если текст не входит целиком в ширину, отведенную прямоугольником, то он не выводится QlikView! Поэтому напишем функцию, которая найдет ближайший пробел в тексте и сделает перенос на вторую строку:
1 2 3 4 5 6 7 |
=DUAL(, IF( LEN(POK_NAME)>30, MID(POK_NAME, 1, 18+Index(MID(POK_NAME,20),` `) ) &chr(10)& MID(POK_NAME, 20+Index(MID(POK_NAME,20),` `) ) POK_NAME ) RangeMax(vAllSum_by_minKO,vAllSum_by_maxKO)*vCenter) |
Вот теперь наш «эффект бабочки» готов!
Расскажите о своих техниках работы с гистограммой в QlikView в комментариях. До новых встреч!
Свежие комментарии