Сегодня Андрей расскажет о том, как сделать диаграмму «водопад» (waterfall) со сложной группировкой измерений. О банальных вариантах «водопада» для QlikView и Qlik Sense мы уже рассказывали, так что наступило время усложнить и в лучших традициях дать материалы для скачивания (в конце поста).
Был у меня случай — попросили сделать диаграмму «водопад». Ну в «водопад» сегодня уже умеют все. Но только этот случай оказался не простой, а с подковыркой. Иначе я бы вам о нем не стал рассказывать.
«Водопад» со сложной группировкой измерений: задача
Короче, задача была поставлена так:
Сделай водопад, да так, чтобы столбцы ранжировались по модулю отклонения, а еще надо, чтобы отображались только первые «N» столбцов, а остальные схлопывались в группу «Остальные».
Чтобы немного упростить задачу, поделим ее на несколько частей:
- Создание специального измерения, которое будем использовать в диаграмме
- Написание вычисляемого измерения с учетом ранжирования столбцов и отображения только топ «N» наибольших отклонений
- Написание формулы расчета отклонений
- Прикручиваем бантики, вишенки и наслаждаемся результатом
Перед тем как приступить к реализации, расскажу почему именно задача на самом деле сложнее, чем кажется на первый взгляд:
- Отобразить только топ «N» значений, а остальное скрыть в «Остальные». Сперва может показаться, что это легко, ведь есть в настройках диаграммы такая вкладка «Dimension limits». Но вот только в диаграмме водопад она предательски не работает — скрываются не только столбцы отклонений, но и столбцы итогов. И это только пол беды, ведь столбец «Остальные» будет всегда находиться в конце диаграммы, а нам нужно чтобы после него еще было итоговое значение «Текущий год».
- Ранжирование отклонений по убыванию. Опять же водопад с первым и последним столбцом сильно усложняет задачу. В сортировке писать отдельное выражение показалось слишком сложно. В итоге сделал через функцию dual() в расчетном измерении.
А теперь, давайте подробно разберемся в реализации каждого конкретного шага.
Шаг 1. Создание специального измерения для диаграммы
В нашей задаче мы будем использовать технику создания специального измерения в скрипте, чтобы затем использовать его в диаграмме.
Эта техника позволит нам реализовать любую логику группировки данных внутри измерения.
В нашем конкретном случае у нас будет две группы измерений:
- «Тоталы» — это начало и конец нашего водопада, возьмем для примера текущий и предыдущий год и назначим этой группе соответствующий признак.
- «Детализация» — это те данные, по которым нам надо посчитать отклонения. Для примера, пускай это будут группы продуктов. Назначим и этой группе соответствующий признак.
Итак, имеем две группы, выглядит это вот так (табличка будет в материалах статьи):
- Столбец «Признак» — содержит признак, по которому мы будем отделять в сет-анализе итоги (первый и последний столбец) от отклонений (все, что посередине)
- Столбец «Название» — содержит название элемента, группируемого в диаграмме. Его мы будем использовать в качестве измерения для столбчатой диаграммы
- Столбец «Код» — это код группы, который мы будем использовать в качестве ключа для ассоциации с таблицей фактов
Теперь, если мы добавим это измерение и нехитрое условие в расчетную часть, то сможем легко реализовать диаграмму водопад. Идем дальше!
Шаг 2. Написание вычисляемого измерения
Вот тут начинаются хардкорные танцы с бубном. Поверьте, чтобы прийти к этому результату пришлось попотеть, перебирая множество вариантов. В итоге описанный результат работает отлично. Используйте на здоровье =)
Давайте разберем, что нам нужно сделать для достижения результата:
- Отделить столбцы итогов от столбцов отклонения, и сделать так, чтобы они в любом случае отображались в нужных местах (в своей реализации я захардкодил и присвоил им индексы сортировки -1000 и 1000)
- Для столбцов отклонений, найти рейтинг модуля отклонения (по убыванию) и отделить все, что меньше некоторого значения (в нашем случае переменная vTop)
- Для оставшихся отклонений (которые по рейтингу ниже переменной vTop) вывести значение «Остальные»
Ниже я привел код реализации этой задачи (с комментариями):
=Aggr(
// Определяем итоговые столбцы, первый и последний
IF(Only({<Признак = {‘Тоталы’}>}Название) = ‘Предыдущий год’,
dual(Only({<Признак = {‘Тоталы’}>}Название),-1000),
IF(Only({<Признак = {‘Тоталы’}>}Название) = ‘Текущий год’,
dual(Only({<Признак = {‘Тоталы’}>}Название), 1000),
// Ранжируем по убыванию модуля отклонения групп продуктов
IF(
Rank(
Fabs(
Sum({<
Признак -= {‘Тоталы’,’Коэффициент’},
Дата = {«>=$(=MakeDate(2017,1,1)))<=$(=MakeDate(2017,12,31))»}
>}[Кол-во])-
Sum({<
Признак -= {‘Тоталы’,’Коэффициент’},
Дата = {«>=$(=MakeDate(2016,1,1)))<=$(=MakeDate(2016,12,31))»}
>}[Кол-во])
)
,1
) <= $(vTop),
// Все, что выше параметра vTop — отображаем в диаграмме
dual(
Only({<Признак -= {‘Тоталы’,’Коэффициент’}>}Название),
Rank(
Fabs(
Sum({<
Признак -= {‘Тоталы’,’Коэффициент’},
Дата = {«>=$(=MakeDate(2017,1,1)))<=$(=MakeDate(2017,12,31))»}
>}[Кол-во])-
Sum({<
Признак -= {‘Тоталы’,’Коэффициент’},
Дата = {«>=$(=MakeDate(2016,1,1)))<=$(=MakeDate(2016,12,31))»}
>}[Кол-во])
)
,1
)
),
// Все, что ниже параметра vTop — складываем в группу «Остальные»
dual(‘Остальные’,999)
)))
,Название)
Пояснения к коду:
- Функция Aggr() использована, чтобы можно было вычислить формулу отклонения в измерении
- Функция Dual() использована, чтобы можно было выполнить сортировку числовому значению (раздел «Numeric Value» на вкладке «Sort»), не повторяя при этом сложные условия в разделе сортировки по выражению (раздел «Expression» на вкладке «Sort»)
- Функция Fabs() — позволяет вычислить модуль отклонения
- Функция Rank() — вычисляет рейтинг каждой группы продуктов и позволяет отсечь ненужные и поместить их в группу «Остальные»
- Переменная vTop — хранит параметр верхней границы для помещения ненужных групп в группу «Остальные». В демо-приложении этот параметр регулируется ползунком.
P.S. Большинство параметров «захардкожено» по причине моей лени при подготовке примера, сорьки.
Шаг 3. Написание конечного выражения
Для написания конечного выражения у меня про запас осталось еще пара бубнов. Посмотрим на скриншот ниже:
Я выделил 3 зоны, которые мы будем разбирать:
- Выражение, как же без него
- Цвет заднего фона (сделаем, чтобы плохое было красным, а хорошее — зеленым, классика)
- Сдвиг столбиков по оси «Х» (сдвигаем на сумму текущего и предыдущих столбцов)
Начнем с выражения (1):
If(WildMatch(Название,’Предыдущий год’,’Текущий год’,’*’),
Pick(
WildMatch(Название,’Предыдущий год’,’Текущий год’,’Коэффициент’,’*’),
// для предыдущего года берем данные 2016-ого
Sum({<Дата = {«>=$(=MakeDate(2016,1,1)))<=$(=MakeDate(2016,12,31))»}>}[Кол-во]),
// для текущего года берем данные 2017-ого
Sum({<Дата = {«>=$(=MakeDate(2017,1,1)))<=$(=MakeDate(2017,12,31))»}>}[Кол-во]),
// для групп продуктов берем отклонение
Sum({<Дата = {«>=$(=MakeDate(2017,1,1)))<=$(=MakeDate(2017,12,31))»}>}[Кол-во])-
Sum({<Дата = {«>=$(=MakeDate(2016,1,1)))<=$(=MakeDate(2016,12,31))»}>}[Кол-во])
),
// если группа не попала в перечень требуемых, то используем эту формулу
// в нашем случае это группа «Остальные»
Sum({<Дата = {«>=$(=MakeDate(2017,1,1)))<=$(=MakeDate(2017,12,31))»}>}[Кол-во])-
Sum({<Дата = {«>=$(=MakeDate(2016,1,1)))<=$(=MakeDate(2016,12,31))»}>}[Кол-во])
)
Пояснения к коду:
- В начале выражения производим проверку входит ли текущий элемент измерения в массив данных, если не входит, то берем формулу для группы «Остальные»
- Функция WildMatch() в комбинации с Pick() использована, чтобы не городить кучу If’ов.
P.S. Большинство параметров «захардкожено» по причине моей лени при подготовке примера, еще раз сорьки 😀
Перейдем к цвету заднего фона (2):
If(Признак = ‘Тоталы’, Blue(),
If(Выражение < 0, Red(),Green()))
Ну тут даже пояснять нечего, все итоговые столбцы (первый и последний) красим синим, остальные — в зависимости от отклонения, если меньше нуля — красный, если больше или равно — зеленым
И напоследок выражение для сдвига столбцов (3):
If(Признак <> ‘Тоталы’,
rangesum(above( «Выражение», 1, rowno() )),
0
)
Тут, в принципе, тоже все ясно как белый день: для всех столбцов отклонений находим отступ, как сумму текущего и всех предыдущих столбцов, иначе отступ 0.
Шаг 4. Результат
В итоге имеем такую вот сельско-колхозную красоту:
Но мы ведь с вами знаем что внутри этого, на первый взгляд, хиленького запорожца, таится гоночный болид формулы 1, который даст фору любому конкуренту 😀
Материалы статьи (скачать):
- QVW-файл с реализацией
- Excel-файл с группировкой
Качайте, пользуйтесь, делитесь статьей 🙂
Автор: Андрей Белобородов
Источник: https://blog.andbel.it/S1EONk9w7
Василий Петренко
B3
Максим
Stepan
Павел Карасев
Свежие комментарии