Был у меня случай - попросили сделать диаграмму «водопад». Ну в «водопад» сегодня уже умеют все. Но только этот случай оказался не простой, а с подковыркой. Иначе я бы вам о нем не стал рассказывать.
Короче, задача была поставлена так:
Сделай водопад, да так, чтобы столбцы ранжировались по модулю отклонения, а еще надо, чтобы отображались только первые «N» столбцов, а остальные схлопывались в группу «Остальные».
Чтобы немного упростить задачу, поделим ее на несколько частей:
- Создание специального измерения, которое будем использовать в диаграмме
- Написание вычисляемого измерения с учетом ранжирования столбцов и отображения только топ «N» наибольших отклонений
- Написание формулы расчета отклонений
- Прикручиваем бантики, вишенки и наслаждаемся результатом
Перед тем как приступить к реализации, расскажу почему именно задача на самом деле сложнее, чем кажется на первый взгляд:
- Отобразить только топ «N» значений, а остальное скрыть в «Остальные». Сперва может показаться, что это легко, ведь есть в настройках диаграммы такая вкладка «Dimension limits». Но вот только в диаграмме водопад она предательски не работает - скрываются не только столбцы отклонений, но и столбцы итогов. И это только пол беды, ведь столбец «Остальные» будет всегда находиться в конце диаграммы, а нам нужно чтобы после него еще было итоговое значение «Текущий год».
- Ранжирование отклонений по убыванию. Опять же водопад с первым и последним столбцом сильно усложняет задачу. В сортировке писать отдельное выражение показалось слишком сложно. В итоге сделал через функцию dual() в расчетном измерении.
А теперь, давайте подробно разберемся в реализации каждого конкретного шага.
Создание специального измерения для диаграммы
В нашей задаче мы будем использовать технику создания специального измерения в скрипте, чтобы затем использовать его в диаграмме.
Эта техника позволит нам реализовать любую логику группировки данных внутри измерения.
В нашем конкретном случае у нас будет две группы измерений:
- «Тоталы» - это начало и конец нашего водопада, возьмем для примера текущий и предыдущий год и назначим этой группе соответствующий признак.
- «Детализация» - это те данные, по которым нам надо посчитать отклонения. Для примера, пускай это будут группы продуктов. Назначим и этой группе соответствующий признак.
И так, имеем две группы, выглядит это вот так (табличка будет в материалах статьи):
- Столбец «Признак» - содержит признак по которому мы будем отделять в сет-анализе итоги (первый и последний столбец) от отклонений (все что посередине)
- Столбец «Название» - содержит название элемента группируемого в диаграмме. Его мы будем использовать в качестве измерения для столбчатой диаграммы
- Столбец «Код» - это код группы, который мы будем использовать в качестве ключа для ассоциации с таблицей фактов
Теперь, если мы добавим это измерение и нехитрое условие в расчетную часть, то сможем легко реализовать диаграмму водопад. Идем дальше!
Написание вычисляемого измерения
Вот тут начинаются хардкорные танцы с бубном. Поверьте, чтобы прийти к этому результату пришлось попотеть, перебирая множество вариантов. В итоге описанный результат работает отлично. Используйте на здоровье :)
Давайте разберем, что нам нужно сделать для достижения результата:
- Отделить столбцы итогов от столбцов отклонения, и сделать так, чтобы они в любом случае отображались в нужных местах (в своей реализации я захардкодил и присвоил им индексы сортировки -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 зоны, которые мы будем разбирать:
- Выражение, как же без него
- Цвет заднего фона (сделаем, чтобы плохое было красным, а хорошее - зеленым, классика)
- Сдвиг столбиков по оси «Хэ» (сдвигаем на сумму текущего и предыдущих столбцов)
Начнем с выражения (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. Большинство параметров «захардкожено» по причине моей лени при подготовке примера, еще раз сорьки :D
Перейдем к цвету заднего фона (2):
If(Признак = 'Тоталы', Blue(), If(Выражение < 0, Red(),Green()))
Ну тут даже пояснять нечего, все итоговые столбцы (первый и последний) красим синим, остальные - в зависимости от отклонения, если меньше нуля - красный, если больше или равно - зеленым
И напоследок выражение для сдвига столбцов (3):
If(Признак <> 'Тоталы', rangesum(above( "Выражение", 1, rowno() )), 0 )
Тут, в принципе, тоже все ясно как белый день: для всех столбцов отклонений находим отступ, как сумму текущего и всех предыдущих столбцов, иначе отступ 0.
Результат
В итоге имеем такую вот сельско-колхозную красоту:
Но мы ведь с вами знаем что внутри этого, на первый взгляд, хиленького запорожца, таится гоночный болид формулы 1, который даст форму любому конкуренту :D
Материалы статьи (скачать):
- QVW-файл с реализацией
- Excel-файл с группировкой
Качайте, пользуйтесь, делитесь статьей :)
Если вам понравилась статья или у вас есть идеи как сделать это лучше, пишите мне на почту: me@andbel.it