Как посчитать процент посещений клиентов по месяцам от своей базы

[#1]

Коллеги, привет! Сломал голову, как это можно реализовать, помогите с решением.

Есть 2 таблицы:

1. Таблица торговых точек с разбивкой по торговым представителям ( из нее мы вычисляем кол-во точек, закрепленных за каждым торговым представителем), имеет ключ для связи ID точки

2. Плоская таблица с фактическими посещениями точек с указанием даты посещения.

Как организовать сводную таблицу, чтобы в строках были торговые представители, в столбце месяцы, а в мерах 3 колонки: а) Количество точек в базе ( по факту считаем кол-во точек из таблицы 1, таблица автоматом разбросает кол-во точек по каждому торговому).  б) Фактическое количество посещенных точек ( тут тоже все понятно, считаем кол-во уникальных торговых точек из таблицы 2).  с) Процент покрытия базы ( Делим б на а)

Но вот незадача, при разбивке по месяцам, таблица напрочь игнорирует ссылку в формуле на подсчет количества точек из таблицы 1, а берет их из таблицы 2. Т.е. по факту значения по месяцам всегда равны, и всегда значение "а", зависит от количества посещенных точек в отображаемом периоде, а мне нужно, чтобы оно было во всех периодах одинаковое, как в исходной таблице 1, для корректного расчета покрытия базы.

Есть идеи, может даже альтернативные?

Заранее спасибо откликнувшимся

[Sergey_Polekhin · Accepted solution]

Ваш вопрос распадается на несколько составляющих:

1. Использование корректной модели
2. Использование корректных формул
3. Корректность работы объекта визуализации
4. Что делать в вашем конкретном случае (workaround)

Теперь по порядку:

Использование корректной модели

Здесь не буду писать много, полагая, что вы лучше понимаете зависимости в ваших данных (возможные у вас реляционные отношения). Поэтому просто обращу внимание на то, что, возможно, связывание таблиц нужно делать не по одному полю, а по нескольким, что позволит вам корректно обрабатывать отношения "многие-ко-многим", В своих примерах я объединяю ваши таблицы по нескольким ключевым полям: ТочкаID, ТорговыйПредставитель (исходное поле "Зона продаж") и "Название клиента 1".

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

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

Корректность формул

1. Общее предупреждение: будьте аккуратны при использовании функции Count() по отношению к ключевым полям. Т.к. бездумно используя функцию Count(), вы можете получить некорректные результаты из-за того, что содержимое ключевых полей наполняется значениями из различных таблиц
2. Для того, чтобы значения функций рассчитывались правильно, нужно не забывать, что их расчет всегда происходит в контексте заданных измерений. Т.е. если в качестве измерений вы используете два поля: Месяц и ТорговыйПредставитель, то любые функции по умолчанию будут рассчитываться в контексте обоих измерений.
   Поэтому если вы хотите считать функции в другом контексте измерений, то этот контекст нужно явно определять. Для этого существует префикс Total (см. пример1)

Корректность работы объекта визуализации

1. Похоже, нужно признать, что текущая версия сводной таблицы в Qlik Sense не вполне корректно обрабатывает префикс Total. Во всяком случае, аналогичный объект визуализации в QlikView позволяет получить искомый результат с использованием таких же выражений (см. рис. нижеt).
   
2. Оформил кейс разработчикам, полагаю, что они исправят этот баг в ближайшее время.
3. Но даже при корректной работе объекта визуализации автоматически создаваемая таблица будет не "вполне симпатичной" для конечного пользователя (см. рис. выше), т.к. рассчитываемые значения общего количества торговых точек вы хотите выводить в контексте месяцев, которые не имеют прямой связи с рассчитываемыми значениями.

Что делать в вашем конкретном случае (workaround)

1. В вашем случае я предложил бы создать дополнительную таблицу в модели, содержащую сочетания значений поля ТорговыйПредставитель всем имеющимся значениям поля Месяц
2. Предложенный подход позволит как обойти недостаток текущей версии объекта визуализации, так и сделать таблицу более понятной конечному пользователю (см. пример 2).

[#3]

Сергей, спасибо, за столь подробный ответ!

Пример 2, безусловно подошел как нельзя лучше. Но и пример 1, вполне рабочий для наших задач.

Для себя уяснил следующее:

1. Я приходил к вашему примеру 1, и имел такой вид таблиц с корректными данными в таблице, но, все верно, как вы и пишите, таблица в первоначальном виде считает как нужно, т.е. все колонки по строке итого по месяцам рассчитывает верно, но как только применяешь фильтр месяц, все слетает... это и не понятно было. См. вложение. Т.е. проблема не только в сводной таблице, а видимо в алгоритме. Т.к. любые другие диаграммы выводят тоже самое.

2. Пример 2, безусловно красивее, но требует понимания, как вы сделали третью таблицу. Я пока начинающий специалист по работе с BI системами, в голове иногда себе не представляю какими должны быть взаимосвязи и как правильно построить вспомогательные таблицы для нужных результатов и визуализаций. Но буду разбираться, в т.ч. и на ваших примерах!

Вам огромное спасибо за помощь и подсказки, а так же успехов вам на работе и в жизни! !

[Sergey_Polekhin]

И Вам Успехов и Удачи!