• Facebook
  • ВКонтакте
  • LinedIN

Sapiens solutions 2017

Анализ особого случая модели "Водопад"

В этой статье будет разобрана задача с известной всем моделью «водопад». Только мы будем анализировать особый случай.

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

 

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

 

  • Создание специального измерения, которое будем использовать в диаграмме

  • Написание вычисляемого измерения с учетом ранжирования столбцов и отображения только топ «N» наибольших отклонений

  • Написание формулы расчета отклонений

  • Подведение результатов своей работы.

 

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

 

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

Ранжирование отклонений по убыванию. Опять же водопад с первым и последним столбцом сильно усложняет задачу. В сортировке писать отдельное выражение показалось слишком сложно. В итоге сделал через функцию dual() в расчетном измерении.

 

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

 

 

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

 

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

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

 

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

 

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

 

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

 

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

 

Столбец «Признак» - содержит признак по которому мы будем отделять в сет-анализе итоги (первый и последний столбец) от отклонений (все что посередине)

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

Столбец «Код» - это код группы, который мы будем использовать в качестве ключа для ассоциации с таблицей фактов

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

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

 

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

  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 - хранит параметр верхней границы для помещения ненужных групп в группу «Остальные». В демо-приложении этот параметр регулируется ползунком.

Написание конечного выражения

 

 Для написания конечного выражения выделяем 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))"}>}[Кол-во])

)

 

 

Пояснения к коду:

В начале выражения производим проверку входит ли текущий элемент измерения в массив данных, если не входит, то берем формулу для группы «Остальные»

 

Перейдем к цвету заднего фона (2):

 

If(Признак = 'Тоталы', Blue(),

If(Выражение < 0, Red(),Green()))

 

 

Дальше выражение для сдвига столбцов (3):

 

If(Признак <> 'Тоталы',

      rangesum(above( "Выражение", 1, rowno() )),

      0

 

  )

 

Эта схема очень проста: для всех столбцов отклонений находим отступ, как сумму текущего и всех предыдущих столбцов, иначе отступ 0.

В результате мы получаем вот это:

 

 

 

 

© Андрей Белобородов

 

Facebook
Please reload

Смотрите также:

Please reload