Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Суммирование значений факта в коридоре Avg+/-3*Stdev

Всем привет!

Столкнулся со следующей задачей - необходимо получить сумму всех продаж (факт Нетто), которые отличаются от среднего значения продажи не более чем на 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 клиента и материала (то есть для одной строки таблицы). При отсутствии выделения в каждой строке таблицы (для клиента и материала) отображаются полные суммы продаж.

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

С уважением,

Роман.

1 Solution

Accepted Solutions
Not applicable
Author

Да, ошибочка вышла. Нужно еще 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

               )        

  )

)

View solution in original post

12 Replies
Eugeny_Ilyin
Creator II
Creator II

День добрый,

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

Not applicable
Author

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

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

Not applicable
Author

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

Not applicable
Author

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

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

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

С уважением,

Р.

sparur
Specialist II
Specialist II

Првиетствую

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

Sum(

     Aggr(

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

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

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

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

                    Нетто

               )

           )

     , Dim1, Dim2, etc)

)

Not applicable
Author

Только конечно же 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 добавьте сами.

Not applicable
Author

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

Попробовал:

=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'а. Не получается сформировать учитываемые в каждой строке отдельные границы коридора. Вот в чем... засада. Надо дальше думать. )

Not applicable
Author

Да, ошибочка вышла. Нужно еще 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

               )        

  )

)

Not applicable
Author

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