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

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

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

Сделай водопад, да так, чтобы столбцы ранжировались по модулю отклонения, а еще надо, чтобы отображались только первые «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