Практика бизнес-анализа для BI-систем

Q: Есть задача посчитать доли по статусам продаж в каждом месяце.
К примеру: имеем следующие данные

Date Статус Sales
29.08.2016 Оплачен 100
30.08.2016 Возврат 200
31.08.2016 Оплачен 300
01.09.2016 Оплачен 400
02.09.2016 Возврат 500

В итоге нужно получить:

Август Сентябрь
Sum([Статус]) Доля, % Sum([Статус]) Доля, %
Итого 600 100% 900 100%
Оплачен 400 67% 400 44%
Возврат 200 33% 500 56%

В QlikSense сделала сводную таблицу, проблемы возникают с подсчетом показателя — Доля, %.
Sum([Sales])/Sum(total [Sales]) не катит, так как Sum(total [Sales]) считает сумму по всей базе, а не по определенному месяцу.
Еще пробовала так: Sum([Sales])/Aggr(Sum([Sales]),[Дата-Дата .autoCalendar.Month]), тоже не получается.
Помогите, пожалуйста.

A: Читаем хелп по sum()
А можно сразу здесь почитать, с примером по вашему случаю.
Вкратце, надо будет указать поле для определения уровня агрегирования Total.
Цитировать
Если слово TOTAL стоит перед аргументами функции, вычисление выполняется по всем возможным значениям, указанным в текущих выборках, а не только в тех, которые относятся к значению текущего измерения, т. е. измерения диаграммы игнорируются.

После префикса TOTAL может быть указан список, включающий одно или несколько имен полей в угловых скобках . Эти имена полей должны быть поднабором переменных измерений диаграммы.

Q: Приветствую есть ли аналог функции СУММЕСЛИ в QV?
Есть список номенклатуры на остаках всего порядка 80 000 артикулов.
Как подсчитать количество разных артикулов по которым была реализация?

A: Надо подсчитать количество уникальных артикулов, по которым была реализация.
count( distinct [признак реализации])

Q: Есть ли возможность организовать выражение, которое находило бы max и min не из диапазона значений, а из набора основных элементов или набора переменных, которые содержат в себе какие-то выражения?

На пальцах:
У меня есть 3 созданных мною основных выражения (двойные кавычки обозначают название):

«SLA»

num((1-if($(tnTicket1)=0,’-‘,($(tnTicket4))/($(tnTicket1)+$(tnTicket2_1)+$(tnTicket2_2)))
),
‘##,00%’
)

«SLAAIR»

num((1-if($(tnTicket1SLA)=0,’-‘,($(tnTicket4SLA))/($(tnTicket1SLA)+$(tnTicket2_1SLA)+$(tnTicket2_2SLA)))
),
‘##,00%’
)

«SLABLPS»

num((1-if($(tnTicket1BLPS)=0,’-‘,($(tnTicket4BLPS))/($(tnTicket1BLPS)+$(tnTicket2_1BLPS)+$(tnTicket2_2BLPS)))
),
‘##,00%’
)

Хотел бы на лист добавить элемент ключевого показателя эффективности, который принимал бы минимальное значение, принимаемое одним из этих основных элементов.

A: Попробуйте использовать функцию rangemin().
Например, при rangemin(1,2,3), данная функция вернёт минимальное значение, равное 1.
Также, можно воспользоваться вот таким выражением:
Min(ValueList(1,2,3))

Q: К сожалению, не удалось найти ответ на просторах форума. Эксперименты тоже не увенчались успехом :'( Есть подозрение, что это делается крайне легко, но как-то не получается((

Подскажите, пожалуйста: возможно ли сделать заголовок диаграммы с изменяющимся параметром? Например, «Количество продаж за *дата*». При этом *дата* — это некая формула, которая берет, например, текущий день или последний день из данных.

A: Конечно можно. Для этого следует прописать выражение возвращающее/формирующее текстовую строку, например:
Код:
=’Данные от ‘&$(vDataZaprosa)&’.’
Т.е. если в заголовке будет выражение начинающееся со знака = , то оно будет вычисляться, иначе просто отображаться.

Q: Всем привет, нужна помощь по получению долей. Есть таблица, в ней у меня указан контрагент и суммы.
Хочу получить, какую долю занимает каждый из них.
Пример:
Контр1 2345 рублей
Контр2 1111 рублей
Контр3 4343 рублей
Контр4 32131 рублей

Нужно добавить колонку, которая будет считать следующее:
Для контр1 — 2345 / Сумма(2345+1111+4343+32131) = сколько то процентов
Для контр2 — 1111 / Сумма(2345+1111+4343+32131) = сколько то процентов и т.д.

A: потребуется либо использовать измерение — период, либо включить условие в выражение фильтра.
Смотрите по справке — Анализ множеств.

Q: Есть формула:
Код:
=Sum( Aggr(
Count( DISTINCT {=$(=vStartDate)<=$(vEndDate)"}>} Product_id)/ Count( DISTINCT TOTAL {=$(=vStartDate)<=$(vEndDate)"}>} Product_id),
Order_id, category_name , Канал_привлечения(Общий) ) )
Как это упростить, чтобы не было 3-х уровней агрегации?

A: Да. Используйте подготовку данных на этапе загрузки

Q: Подскажите как посчитать для измерения коммутатор число контекстов в которых разные платы.
В приведенном примере таковых 2.

Коммутатор Контекст плата
MRC_09 2106136397 :8
MRC_09 2106136378 :9
MRC_09 2106136358 :10
MRC_09 2106136358 :11
MRC_09 2106136354 :9
MRC_09 2106136335 :10
MRC_09 2106136335 :10
MRC_09 2106136313 :10
MRC_09 2106136313 :11

A: Count(DISTINCT {<Контекст=,Контекст={"=Aggr(Count(Distinct плата), Контекст)>=2″}>} Контекст)

Q: Считаю число Call ID на определенном провайдере с определенного направления
count({<Провайдер={"MSK-1"}>} if(Направление=’Вход (усп)’, [Call ID]))
А как посчитать числоCall ID на всех провайдерах кроме 3-х определенных и только для 2-х направлений?

A: либо тот же Set Analysis, только:
{‘*’}-{‘Значение 1’}-{‘Значение 2’}-{‘Значение 3’}

Возможно с галочками где-то не так. Исключение из всего множества значений.

Q: строю график по выражению
=if(Count ([Call ID])>10,count(if(Направление=’Исх (усп)’, [Call ID]))*100/(count(if(Направление=’Исх (не усп)’, [Call ID]))+count(if(Направление=’Исх (усп)’, [Call ID]))))
измерение это счет.
Как построить график, в которым измерением бы было число счетов, а выражение тоже?

A: почитайте про использование функции aggr().

Q: Описание и логика
В заказах присутствую товары из разных категорий. Для определения кол-ва заказов (Orders) считаю веса:

Например, рассчитываем вес (кол-во заказов) для раздела «Home» : 1\2 + 1\3 + 1 = 1.83.
В таблице выражение вот такое:
Код:
Sum(Aggr(
Count( DISTINCT Product_id) / Count( DISTINCT TOTAL Product_id),
Order_id, category_name))

Тут все хорошо.

Проблема
Нужно определить кол-во новых клиентов за выбранный интервал.
Логика расчета должна быть аналогичная, т.е. через веса. Новый клиент — дата создания первого заказ в интервал если попадает.

Т.е. если интервал выбран 01.01.2015 — 02.01.2015 , то, как я понимаю, клиент с номером 1 уже не должен учитываться как-то в расчете. Как рассчитать этих новых клиентов и заполнить второй стоблик?
Скриншот прилагается. Буду очень благодарен за помощь!

Исходные данные такие:

Order:
User_id Order_id Date_insert
1 1 01.01.2015 12:33:12
1 4 01.01.2014 12:33:12
2 2 01.01.2015 11:23:11
3 3 02.01.2015 13:12:16

Basket:
Order_id Product_id
1 1
1 2
2 1
2 2
2 3
3 1
4 1

Category:
Product_id category_name
1 Home
2 Garden
3 Jewelry

A: Вроде бы сам разобрался. А вдруг кому пригодится, вот так можно:
Код:
Sum(Aggr(
Count( DISTINCT {}Date)>=vStartDate»}>} Product_id) /
Count( DISTINCT total {}Date)>=vStartDate»}>} Product_id) ,
Order_id, category_name))

Q: Строю диаграмму по выражению.
count(if(Направление=’Исх (усп)’, [Call ID]))*100/(count(if(Направление=’Исх (не усп)’, [Call ID]))+count(if(Направление=’Исх (усп)’, [Call ID]))).
Надо, чтобы при построении графика учитывались только данные, которые удовлетворяют условию Count ([Call ID])>1000. Как это сделать?

A: Можно использовать Set Analysis и в каждый Count добавить {<ИзмерениеАгрегации={"=Count ([Call ID])>1000″}>}. Будет что-то типа следующего выражения
Код:
count( {<Направление={"=Count ([Call ID])>1000″}>} if(Направление=’Исх (усп)’, [Call ID]))*100
/
(count( {<Направление={"=Count ([Call ID])>1000″}>} if(Направление=’Исх (не усп)’, [Call ID]))
+
count( {<Направление={"=Count ([Call ID])>1000″}>} if(Направление=’Исх (усп)’, [Call ID]))).
Тут мы оставляем только те Направления, по которым Count ([Call ID])>1000

Я бы ещё и конструкцию IF убрал, а вместо нее добавил вы условия в СА
Например:
Код:
count( {<Направление={'Исх (усп)'}*{"=Count ([Call ID])>1000″}>}[Call ID])*100
/
(count( {<Направление={'Исх (не усп)'}*{"=Count ([Call ID])>1000″}>} [Call ID])
+
count( {<Направление={'Исх (усп)'}*{"=Count ([Call ID])>1000″}>} [Call ID]))

Q: Подскажите, пожалуйста, ответ на следующий вопрос. Как в кликсенс построить диаграмму, в которой удельные веса будут считаться по временным периодам. В частности есть таблица с данными грузоперевозок, в ней есть маршруты машинами разной грузоподъёмности за разные периоды времени. Стоит задача вывести удельный вес в грузообороте машинами разной грузоподъёмности помесячно. У меня получается вывести только удельный вес по итогу за всё время следующим образом sum([Вес])/sum(total [Вес]), и вношу в диаграмму грузоподъёмность и месяц. Как преобразовать эту формулу на помесячный расчёт

A: Должно работать,
Код:
Sum([Sum-Вес])/Sum(total <Месяц> [Sum-Вес])
на вашем примере проверено — все ОК.

Q: Имеются такие данные:
Client, Product, Date, Sales
1, Apple, 01-01-2015, 750
1, Mango, 01-02-2015, 450
1, Limon, 01-02-2015, 350
2, Apple, 01-01-2015, 1750
2, Mango, 01-02-2015, 550
3, Limon, 01-01-2015, 250

(Выбираю Limon) Выражением
Код: [Выделить]
sum( {}) >} Sales)
я оставляю тех, кто покупал Limon. Это 1-ый и 3-ий клиент.

Вопрос: Как мне оставить тех, кто покупал только Limon и ничего кроме. Или, например, только тех, кто покупал Limon и Mango вместе ( среди покупок именно два товара вместе)?

P.S.: может есть другие варианты сделать так, чтобы выборка в списке «Product» работала не как»Или», а как «И» ? Возможно как-то переключать эти режимы?

A: Попробуйте через операции с модификаторами множества.
Код:
sum({ * }) >} Sales)

либо следующее выражение
Код:
sum({}) >} Sales)

Q: Нередко возникает вопрос о применении функции Qlikview AGGR(). В сети можно найти интересные примеры, но не понимая принципа работы AGGR() разобраться в них непросто.
Описание функции на сайте help.qlik.com

На простом примере начнем знакомство с этой функцией.

Возьмем абстрактный пример — факт чего-либо (# Сумма) по двум измерениям (Группа, Подгруппа)
(Извините, но вы не имеете доступа к галерее)
Загружаем данные в приложение Qlikview и создаем простую таблицу на три колонки:
(Извините, но вы не имеете доступа к галерее)
Мы получили данные о результатах работы групп по подгруппам с итогом.
Что еще мы можем получить из этого простого набора данных?

Отсортировав данные по сумме получаем минимальное и максимальное значение (13 237 и 96 040).
Включив опцию «Доля» для выражения суммы получим процент суммы для каждой строки в общей масссе. Того же результата можно добиться используя выражение —
Код:
Sum ([# Сделки])/Sum (TOTAL [# Сделки])
(Извините, но вы не имеете доступа к галерее)
Следующий вопрос — чьи результаты меньше 50% и более 80% от максимального показателя? Т.е. кто насколько отстает от лидера?
Т.е. требуется определить долю суммы каждого сочетания Группа-Подгруппа относительно максимального результата (отношение к 96 040).
Решается этот вопрос довольно просто с применением функции Aggr().
Код:
Sum([# Сумма]) / Max(TOTAL Aggr(Sum([# Сумма]), Группа, Подгруппа))
Почему не стоит использовать в знаменателе формулу max (TOTAL [# Сумма]) , показано на рисунке.
(Извините, но вы не имеете доступа к галерее)
Теперь следует разобраться, как работает эта функция. Согласно описанию, функция aggr() возвращает виртуальную таблицу из значений выражения рассчитанного по измерениям, указанным в качестве параметров. Аналогично выражению GROUP BY оператора SELECT SQL.
Т.е. запрос:
Код:
LOAD sum([# Сумма]) Resident SampleTable1 Group By Группа, Подгруппа
Дает тот же результат что и выражение:
Код:
Aggr(Sum([# Сумма]), Группа, Подгруппа)
Возвращаясь к ответу на поставленный вопрос, как определить группу лидеров и отстающих. Самое простое решение — использование визуальных подсказок для верхней и нижней границы, как показано на рисунке.
(Извините, но вы не имеете доступа к галерее)
Для отладки при составлении выражений можно использовать списки для просмотра результатов расчетов функции AGGR(). Здесь нет ничего сложного — используем выражение вместо поля при создании списка:
(Извините, но вы не имеете доступа к галерее)

Это самый простой случай использования функции AGGR(). На практике ее можно использовать и при создании измерений и для более сложных вычислений.
Если есть интерес, готов рассмотреть другие ситуации.

A: Использование AVG() без предварительного вычисления итогов внутри группы функцией AGGR(), может привести к ошибочным результатам.

И еще один пример с расчетом среднего:
(Извините, но вы не имеете доступа к галерее)
Как видим, среднее подсчитано в трех вариантах, и каждый может быть справедлив, в зависимости от ситуации и задачи.

Формулы для выражений:
Код:
Sum ([# Сумма])
Avg ([# Сумма])
avg(Aggr(Sum([# Сумма]), Группа, Подгруппа))
avg(Aggr(Sum([# Сумма])/Count(Подгруппа), Группа, Подгруппа))

Q: Дано:

Client, Product, Date, Sales
1, Apple, 01-01-2015, 750
1, Mango, 01-02-2015, 450
1, Limon, 01-02-2015, 350
2, Apple, 01-01-2015, 1750
2, Mango, 01-02-2015, 550
3, Limon, 01-01-2015, 250

Limon покупали все 3 клиента. А как мне найти тех клиентов, которые покупали и Limon и Mango ? Причем именно два товара вместе ( 2-ой и 3-ий клиент уже не подойдут).

A: Если в Дименшене оставить Client и прописать.

Код: [Выделить]
if(sum( {}) >} Sales)> 0,
if (sum( {}) >} Sales)>0,
sum(Sales),
0))

То суммы будут только у клиентов которые купили и то и то. Но правда если клиентов схлопнуть до других показателей, то данные могут разъехаться.

Найти решение у бизнес-партнера QlikTech (QlikView) в России.

Форум разработчиков QlikView и Qlik Sense. Получите ответы на все вопросы по QlikView и Qlik Sense!