Сегодня Андрей расскажет о том, как сделать диаграмму «водопад» (waterfall) со сложной группировкой измерений. О банальных вариантах «водопада» для QlikView и Qlik Sense мы уже рассказывали, так что наступило время усложнить и в лучших традициях дать материалы для скачивания (в конце поста).

Был у меня случай — попросили сделать диаграмму «водопад». Ну в «водопад» сегодня уже умеют все. Но только этот случай оказался не простой, а с подковыркой. Иначе я бы вам о нем не стал рассказывать.

«Водопад» со сложной группировкой измерений: задача

Короче, задача была поставлена так:

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

Чтобы немного упростить задачу, поделим ее на несколько частей:

  1. Создание специального измерения, которое будем использовать в диаграмме
  2. Написание вычисляемого измерения с учетом ранжирования столбцов и отображения только топ «N» наибольших отклонений
  3. Написание формулы расчета отклонений
  4. Прикручиваем бантики, вишенки и наслаждаемся результатом

Перед тем как приступить к реализации, расскажу почему именно задача на самом деле сложнее, чем кажется на первый взгляд:

  1. Отобразить только топ «N» значений, а остальное скрыть в «Остальные». Сперва может показаться, что это легко, ведь есть в настройках диаграммы такая вкладка «Dimension limits». Но вот только в диаграмме водопад она предательски не работает — скрываются не только столбцы отклонений, но и столбцы итогов. И это только пол беды, ведь столбец «Остальные» будет всегда находиться в конце диаграммы, а нам нужно чтобы после него еще было итоговое значение «Текущий год».
  2. Ранжирование отклонений по убыванию. Опять же водопад с первым и последним столбцом сильно усложняет задачу. В сортировке писать отдельное выражение показалось слишком сложно. В итоге сделал через функцию dual() в расчетном измерении.

А теперь, давайте подробно разберемся в реализации каждого конкретного шага.

Шаг 1. Создание специального измерения для диаграммы

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

Эта техника позволит нам реализовать любую логику группировки данных внутри измерения.

В нашем конкретном случае у нас будет две группы измерений:

  • «Тоталы» — это начало и конец нашего водопада, возьмем для примера текущий и предыдущий год и назначим этой группе соответствующий признак.
  • «Детализация» — это те данные, по которым нам надо посчитать отклонения. Для примера, пускай это будут группы продуктов. Назначим и этой группе соответствующий признак.

Итак, имеем две группы, выглядит это вот так (табличка будет в материалах статьи):

  • Столбец «Признак» — содержит признак, по которому мы будем отделять в сет-анализе итоги (первый и последний столбец) от отклонений (все, что посередине)
  • Столбец «Название» — содержит название элемента, группируемого в диаграмме. Его мы будем использовать в качестве измерения для столбчатой диаграммы
  • Столбец «Код» — это код группы, который мы будем использовать в качестве ключа для ассоциации с таблицей фактов

Теперь, если мы добавим это измерение и нехитрое условие в расчетную часть, то сможем легко реализовать диаграмму водопад. Идем дальше!

Шаг 2. Написание вычисляемого измерения

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

Давайте разберем, что нам нужно сделать для достижения результата:

  1. Отделить столбцы итогов от столбцов отклонения, и сделать так, чтобы они в любом случае отображались в нужных местах (в своей реализации я захардкодил и присвоил им индексы сортировки -1000 и 1000)
  2. Для столбцов отклонений, найти рейтинг модуля отклонения (по убыванию) и отделить все, что меньше некоторого значения (в нашем случае переменная vTop)
  3. Для оставшихся отклонений (которые по рейтингу ниже переменной 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. Выражение, как же без него
  2. Цвет заднего фона (сделаем, чтобы плохое было красным, а хорошее — зеленым, классика)
  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, который даст фору любому конкуренту 😀

Материалы статьи (скачать):

  1. QVW-файл с реализацией
  2. Excel-файл с группировкой

Качайте, пользуйтесь, делитесь статьей 🙂

Автор: Андрей Белобородов

Источник: https://blog.andbel.it/S1EONk9w7