Формулы мат.статистики в акцентах ячеек

Формулы написаны на примере показателя "Количество клиентов" в разрезе ""S_ORG_EXT Dim"."DIVN_TYPE_CD""




Количество клиентов: Показатель

"S_ORG_EXT Fact"."Количество клиентов" = Показатель

Уникальное количество значений атрибута: Атрибут

count(count(distinct Атрибут))

Среднее значение показателя на атрибут:

sum(Показатель)/count(count(distinct Атрибут))

Отклонение от среднего:

SQRT(sum((Показатель - sum(Показатель)/count(count(distinct Атрибут)))*(Показатель - sum(Показатель)/count(count(distinct Атрибут))))/count(count(distinct Атрибут)))

Предел разброса верхний:

sum(Показатель)/count(count(distinct Атрибут)) + SQRT(sum((Показатель - sum(Показатель)/count(count(distinct Атрибут)))*(Показатель - sum(Показатель)/count(count(distinct Атрибут))))/count(count(distinct Атрибут)))

Предел разброса нижний:

sum(Показатель)/count(count(distinct Атрибут)) - SQRT(sum((Показатель - sum(Показатель)/count(count(distinct Атрибут)))*(Показатель - sum(Показатель)/count(count(distinct Атрибут))))/count(count(distinct Атрибут)))

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

Sigma - предел нормы, по умолчанию = 1 (100%)

sum(Показатель)/count(count(distinct Атрибут)) + Sigma*SQRT(sum((Показатель - sum(Показатель)/count(count(distinct Атрибут)))*(Показатель - sum(Показатель)/count(count(distinct Атрибут))))/count(count(distinct Атрибут)))

Нижний предел разброса с учетом нормы:

Sigma - предел нормы, по умолчанию = 1 (100%)

sum(Показатель)/count(count(distinct Атрибут)) - Sigma*SQRT(sum((Показатель - sum(Показатель)/count(count(distinct Атрибут)))*(Показатель - sum(Показатель)/count(count(distinct Атрибут))))/count(count(distinct Атрибут)))

Проверка атрибута на минимальное/максимальное значение

case

when Показатель = min(Показатель) then 'Min'

when Показатель = max(Показатель) then 'Max'

when Показатель =  min(Показатель) and  Показатель = max(Показатель)

then 'Norm'

end

Проверка атрибута на выбросы пределы разброса

case

when Показатель < sum(Показатель)/count(count(distinct Атрибут)) - SQRT(sum((Показатель- sum(Показатель)/count(count(distinct Атрибут)))*(Показатель- sum(Показатель)/count(count(distinct Атрибут))))/count(count(distinct Атрибут)))
then 'MinPr'

when Показатель > sum(Показатель)/count(count(distinct Атрибут)) + SQRT(sum((Показатель- sum(Показатель)/count(count(distinct Атрибут)))*(Показатель- sum(Показатель)/count(count(distinct Атрибут))))/count(count(distinct Атрибут)))
then 'MaxPr'

end


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

Для этого на каждый такой показатель делаем два доп.столбца:
Мин/Мах и Пр
со следующими значениями:

case when Показатель = min(Показатель) then 'Min'  when Показатель = max(Показатель) then 'Max'  when Показатель =  min(Показатель) and  Показатель = max(Показатель) then 'Norm' end

case when Показатель < sum(Показатель)/count(count(distinct Атрибут)) - SQRT(sum((Показатель- sum(Показатель)/count(count(distinct Атрибут)))*(Показатель- sum(Показатель)/count(count(distinct Атрибут))))/count(count(distinct Атрибут))) then 'Pr'  when Показатель > sum(Показатель)/count(count(distinct Атрибут)) + SQRT(sum((Показатель- sum(Показатель)/count(count(distinct Атрибут)))*(Показатель- sum(Показатель)/count(count(distinct Атрибут))))/count(count(distinct Атрибут))) then 'Pr' end

Далее на столбце делаем условное форматирование из трех строк:
1. Если показатель Мин/Мах = Min - то в свойствах ставим флажок минимального значения
2. Если показатель Мин/Мах = Мах - то в свойствах ставим флажок максимального значения
3. Если показатель Пр - Pr = то в свойствах ставим цвет поля с выходи за пределы