Россия и СНГ

[#1]

Всем привет!

Столкнулся со следующей задачей - необходимо получить сумму всех продаж (факт Нетто), которые отличаются от среднего значения продажи не более чем на 3 сигмы, отличающиеся необходимо включить в сумму обрезав/дополнив до соответствующей границы коридора.

Отдельные поля со средним значением (avg), сигмой (stdev), верхней (avg+3stdev) и нижней (avg-3stdev) границами коридора, минимальным (min) и максимальным (max) значениями продажи считаются "на ура" для кода клиента и кода материала. А вот попытка в одном поле получить сумму по условию коридора продажи (с проверкой на попадание суммируемого значения в коридор) - не увенчиваются успехом. Либо Клик ругается на границу интервала в условии, либо просто ее игнорирует.

Следующая запись отображает коммент "Error in Expression":

=sum(if(Нетто > avg(Нетто)+3*Stdev(Нетто),

            avg(Нетто)+3*Stdev(Нетто),

            if(Нетто < avg(Нетто)-3*Stdev(Нетто),

               avg(Нетто)-3*Stdev(Нетто),

               Нетто

               )

           )

       )

Добавление идентификатора total решает проблему ошибки в выражении, но не решает исходной задачи:

=sum(if(Нетто > avg(total Нетто)+3*Stdev(total Нетто),

            avg(total Нетто)+3*Stdev(total Нетто),

            if(Нетто < avg(total Нетто)-3*Stdev(total Нетто),

               avg(total Нетто)-3*Stdev(total Нетто),

               Нетто

               )

           )

       )

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

Заранее благодарен за идеи.

С уважением,

Роман.

[Eugeny_Ilyin]

День добрый,

можете пример данных привести?

[#3]

Евгений, спасибо за внимание к теме.

Файл подготовил и залил. Как только пройдет модерацию, сразу опубликую ссылку.

[#4]

Исходные данные (TEST_Data.xlsx) и моделька (Test_AVG_3_STDEV.qvw) для тестирования (факты переименовал).

[#5]

Всем доброго времени суток!

Есть ли у кого соображения по существу? Сам пока не могу придумать, как побороть супостата. )

Спасибо за идеи!

С уважением,

Р.

[sparur]

Првиетствую

я бы попробовал вложенную аггрегацию через AGGR. Схематично как-то так:

Sum(

     Aggr(

          if(Нетто > avg(Нетто)+3*Stdev(Нетто),

               avg(Нетто)+3*Stdev(Нетто),

               if(Нетто < avg(Нетто)-3*Stdev(Нетто),

                    avg(Нетто)-3*Stdev(Нетто),

                    Нетто

               )

           )

     , Dim1, Dim2, etc)

)

[#7]

Только конечно же aggr не перед if а перед aggregated functions

=sum(

    if(

    Sales > aggr( avg(Sales)+3*stdev(Sales), ClientID,ProductCode )

    ,aggr( avg(Sales)+3*stdev(Sales), ClientID,ProductCode )

    ,Sales

    )

)

второй вложенный if добавьте сами.

[#8]

Анатолий, Егор, спасибо за вариант. Но не он. )

Попробовал:

=sum(

    if( Sales > aggr( avg(Sales)+3*stdev(Sales), ClientID,ProductCode ),

         aggr( avg(Sales)+3*stdev(Sales), ClientID,ProductCode ),

         if( Sales < aggr( avg(Sales)-3*stdev(Sales), ClientID,ProductCode ),

              aggr( avg(Sales)-3*stdev(Sales), ClientID,ProductCode ),

              Sales

         )

    )

)

Результат: полное суммирование. Все равно, что

=sum(Sales)

написать. Под суммой получается сравнение каждого значения Sales с каждым агрегированным по клиенту/продукту элементом aggr'а. Не получается сформировать учитываемые в каждой строке отдельные границы коридора. Вот в чем... засада. Надо дальше думать. )

[#9 · Accepted solution]

Да, ошибочка вышла. Нужно еще nodistinct дополнить. Вроде так правильно:

= sum( 

if( Sales > (aggr(nodistinct avg(Sales)+3*Stdev(Sales),ClientID,ProductCode))

          , (aggr(nodistinct avg(Sales)+3*Stdev(Sales),ClientID,ProductCode))

          ,if( Sales < (aggr(nodistinct avg(Sales)-3*Stdev(Sales),ClientID,ProductCode))

                       , (aggr(nodistinct avg(Sales)-3*Stdev(Sales),ClientID,ProductCode))

                       , Sales

               )        

  )

)

[#10]

Перечитал еще раз формулировку проблемы. Вам avg и stdev нужно считать по всему датасету или отдельно на каждую строку таблицы?