Основы работы в QlikView

Работа в режиме «Аналитической витрина»
Занятие 1. Основы работы в QlikView
Шибеко Виктор
Системный аналитик «Аплана»
Группа компаний АйТи

Основные элементы раздела «Анализ»
Для «Работника ДЗА» и «Работника ДЗА. Изменение»: Аналитическая витрина – это «Анализ» и «Отчеты».
Для «Работника ДЗА. Изменение»: + «Шаблоны отчетов».
Раздел «Анализ»:
Файлы QlikView со структурой данных и загруженными в них данными;
Загрузка – автоматическая, по расписанию
Источник данных – база данных в SharePoint. Объем данных: справки о доходах, кадровые анкеты, декларации конфликта интересов, декларации заинтересованности и т.д.
Каждый файл состоит из областей:
Рабочая область;
Панель инструментов.

Работа с панелью аналитической витрины. Общие принципы
Основной принцип работы в QlikView построен на работе ассоциативной модели – все связанные данные оперативно отображаются в различных разрезах;
Работа фильтра – выбор значения, очистка значения;
Три цвета – серый (данные не связаны с выборкой), белый (данные связаны с текущей выборкой), зеленый – выбранные в фильтре данные;
Панель инструментов – основные функции и набор инструментов;
Сохранить и загрузить выборку (фильтры) – «Добавить закладку» в верхней части витрины.

Работа в разделе «Анализ» на примере отчета

Работа с панелью аналитической витрины. Подготовка аналитической витрины к работе
Добавление вкладки – Кнопка «+» справа сверху. Новая пользовательская вкладка;
Добавление объекта – на панели инструментов вверху нажать на «+». В списке – перетащить элемент на свободное место в аналитической витрине;
Добавить «Текущие выборки» - настройка фильтров;
Настройка свойств объекта через контекстное меню «Свойства…» – Имеются основные настройки и расширенные настройки. Состав зависит от типа объекта
Добавление списка – предназначен для формирования нужного разреза информации на витрине. Списки - список «ФИО сотрудника» и «Перечень».

Работа с панелью аналитической витрины. Настройка таблицы
Добавление объекта – Сводная таблица. При добавлении по умолчанию заполняется данными. Переместить на нужное место на аналитической витрине;
Описание настроек для сводной таблицы:
«Сводная таблица» - определяются в зависимости от выбранного объекта:
Измерения – столбцы и строки, которые отображаются в таблице;
Выражения – те рассчитываемые значения, которые отображаются в ячейках таблицы с учетом выбранных измерений;
«Представление» - оформление стилей и оформления таблицы;
«Заголовок» – оформление заголовка сводной таблицы;
«Опции» - настройка доступа к элементу и к изменению положения элемента.

Наполнение таблицы данными. Настройка измерений
Добавление измерения – Нажать на «+»
Новое измерение – новый разрез информации, в сводной таблице – новая строка или новый столбец;
Порядок следования измерений – важен: определяет вложенность разрезов информации
Переопределение порядка вложенности разрезов сводных таблиц. Опции «Разрешить Перем/Ред» - должна быть включена. Перетягивание заголовков сводной таблицы (измерения) в нужное место – нажать на заголовок и, не отпуская его, перетащить его на нужное место (будет отображаться зеленая горизонтальная или вертикальная линия)
Показать итоги по нужному измерению – Выбрать измерений – Дополнительные параметры – «Показать част. суммы»

Настройка выражений
Добавление выражения – Нажать на «+»
Новое выражение – новая рассчитываемая ячейка для каждого выбранного измерения (новая рассчитываемый элемент, в сводной таблице);
Порядок следования выражений – определяет порядок следования вычисляемых столбцов в таблице;
Выражения – строятся на языке со специальным синтаксисом;
Выражения можно складывать, умножать и т.д. друг с другом: + - сложение, - - вычитание, * - умножение, / - деление;
Sum() – сумма, Count() – подсчет количества, Avg() – среднее значение, Min() – минимальное значение, Max() – максимальное значение и т.д.;
Ограничение множеств задается с помощью анализа множеств – специального языка с заданным синтаксисом;
Перед выражением должен стоять символ = (равно)
Перед тем, как формировать вычисления в Аналитической витрине– важно понять, каким образом функция рассчитывается

Добавление выражений в сводную таблицу
Столбец 1 – сумма всех доходов в указанном разрезе. Функция – sum(), показатель – [Величина дохода];
Выбрать функцию вычисления или агрегации. Например, Sum();
Выбрать поле или измерение, над которым требуется произвести вычисление. Поля из нескольких слов заключаются в квадратные скобки [ ]. Поле заключается в круглые скобки функции агрегации. Например, Sum([Величина дохода, руб])
Столбец 2 – сумма всех доходов в указанном разрезе по отношению ко всем доходам:
Агрегируемые функции можно вычислять друг с другом;
Для расчета без учета разрезов используется префикс total. Записывается внутри круглых скобок до рассчитываемой величины.
Для расчета с учетом разреза после префикса total должен идти наименование параметра (разреза) в треугольных скобках

Работа с условными выражениями
Подсвечивание столбца «Доля от СГД». Условия задаются с помощью ключевого слова IF
Синтаксис:
If (условие, действие, когда условие верно, действие, когда условие не выполняется>)
Действие, когда условие не выполняется можно не указывать;
Пример if:
IF (SUM([Величина дохода])>=10000, ‘да’, ‘нет’)
Можно использовать несколько вложенных if:
IF (SUM([Величина дохода])>=10000, ‘наверное’,
IF (SUM([Величина дохода]) >= 20000, ‘да’, ‘нет’)
Можно использовать несколько условий одновременно;
Условия разделяются операторами OR (или) AND (и);
Пример if с несколькими условиями:
IF (SUM([Величина дохода])>=10000 AND SUM([Величина дохода])<=20000, ‘В допуске’, ‘Вне допуска’)
Для подкрашивания ячеек – перейти в расширенные настройки для выбранного выражения и в поле «Цвет фона» ввести условие. Подкрашивание – функции Red(), Green(),… или RGB (R, G, B), где R, G, B – соответственно коды красного, зеленого и голубого компонентов цвета)
Ввести условие: «Если значение в поле больше 25% - подкрасить красным цветом» (код цвета - RGB (255,0,0)

Сложные выражения
Столбец «Отклонение (в ср. с ППГ)». Порядок расчета - разница в доходе по отношению к текущему году, т.е.:
(Данные за текущий год – Данные за прошлый год)/Данные за текущий год, выраженные в %
Необходимо для выбранного выражения получить получить данные в разрезах «Вида дохода» и «Отчетного периода» за предыдущий год:
SUM([Величина дохода])
Функция ABOVE – возвращает значение на определенное количество пунктов выше в указанной в скобке величине
ABOVE (SUM([Величина дохода]))
Функция AGGR – возвращает диапазон значений (по факту - виртуальную таблицу) с выражениями и указанными измерениями. Формат записи AGGR(Выражение, Измерения). Для примера –
AGGR(ABOVE(SUM([Величина дохода, руб])), [Вид дохода], [Отчетный период])) – функция создаст виртуальную таблицу с двумя измерениями [Вид дохода] и [Отчетный период]. Порядок измерений важен! В ячейках таблицы для каждого [Вид дохода] будут указано значение Сумма [Величина дохода], за отчетный [Отчетный период] на одну строчку выше, т.е. на предыдущий год

Сложные выражения
Итоговая функция для суммы дохода для предыдущего периода относительно выбранной даты AGGR(ABOVE(SUM([Величина дохода, руб])), [Вид дохода], [Отчетный период])
Итоговая формула: (SUM([Величина дохода, руб])- AGGR(ABOVE(SUM([Величина дохода, руб])), [Вид дохода], [Отчетный период]))/SUM([Величина дохода, руб])*100
Для упрощения допускается ссылаться на ячейки в таблице – функция Column (порядковый номер выражения в списке): Column(1).
Формулу можно сократить до ((Column(1)- AGGR(ABOVE(SUM([Величина дохода, руб])), [Вид дохода], [Отчетный период]))/Column(1))*100
Столбец «Маркер отклонения». Каждому диапазону соответствует свой рисунок, заранее подгружаемый в QlikView. Порядок расчета:
Если показатель «Отклонение» от 0 до 20 % включительно – зеленый кружок; 'qmem://
/BuiltIn/led_g.png'
Если показатель «Отклонение» от 20 до 50% включительно – желтый кружок; 'qmem://
/BuiltIn/led_o.png'
Если показатель «Отклонение» более 50% - красный кружок; 'qmem://
/BuiltIn/led_r.png'
Иначе (нет данных) – серый кружок. 'qmem://
/BuiltIn/led.png’
С помощью вложенных условий и ссылки на предыдущее значение столбца описать логику заполнения столбца;
Для отображения данных в виде рисунка – Дополнительный настройки выражения – Представление – «Рисунок».

Настройки оформления и другие настройки
Формат числа (числовой, денежный, процентный) – для каждого выражения задается отдельно. Расширенные настройки – Формат числа;
Выравнивание столбца: подписи и значения. Расширенные настройки – Выражение;
Подписи к столбцам - Расширенные настройки – Выражение;
Представление – оформление фона, стиля таблицы, способа отображения нулевых, отсутствующих значений;
Представление – оформление шрифтов и заголовков таблицы – цвет, тип шрифта текста таблицы, заголовка и т.д.
Заголовок – оформление заголовка: название заголовка, пункты меню в заголовке, выбор смены типа объекта;
Опции – настройки доступа к объекту, совместное использование объектов, настройки изменения состояния объекта (перемещение, копирование и т.д.)
Изменение размера элементов

Работа в разделе «Анализ» на примере отчета
Добавление объекта – Круговая диаграмма. При добавлении по умолчанию заполняется данными. Переместить на нужное место на аналитической витрине;
Описание настроек для сводной таблицы:
«Круговая диаграмма» - определяются в зависимости от выбранного объекта:
Измерения – разбиение диаграммы на разрез: отчетные периоды и виды доходов;
Выражения – те рассчитываемые значения, которые отображаются в на круговой диаграмме;
«Представление» - оформление стилей и оформления таблицы;
«Заголовок» – оформление заголовка сводной таблицы;
«Опции» - настройка доступа к элементу и доступа к изменению положения элемента.

Наполнение таблицы данными. Настройка измерений
Добавление измерения – Нажать на «+»
Новое измерение – новый разрез информации, в сводной таблице – новая иерархия круговой диаграммы;
Порядок следования измерений – важен: определяет вложенность разрезов информации;
Дополнительно – Измерение – Первый размер для настройки решетки – Фиксированное число столбцов и строк – для корректного отображения диаграмм;
Дополнительно – Представление – Вид в 3D поверхность

Наполнение таблицы данными. Ввод выражений
Добавление выражения – Нажать на «+»
Формула расчета – по аналогии с столбцом «Доля от СГД»: SUM([Величина дохода, руб]) / SUM (total <[Отчетный период]> [Величина дохода, руб])
Отображение значений над секторами диаграммы – Дополнительные настройки выражения – Настройки отображения значений
Для корректного формата отображения значений – Доп.настройки выражения – Формат числа. Формат – «Числовой», «в %» – включено. Для отображения двух знаков после запятой – задать формат «#.##%»
Настройки оформления – по аналогии со сводной таблицей

Формирование ограничений. Анализ множеств
Записывается в виде выражений
Содержит условия
Накладывает ограничения
Работает с загруженными в QlikView данными
Необходим для:
ограничения определенных измерений;
исключения некоторых значений;
задания условий отображения.
фиксации зависимости от выборки (фильтра).
Sum({$<[Отчетный период]={"201*"}>}[Величина дохода, руб])
Анализ множеств

Анализ множеств. Синтаксис
Sum({$<[Отчетный период]={"201*"}>}[Величина дохода, руб])
Порядок добавления:
Выбрать функцию вычисления или агрегации. Например, Sum();
Выбрать поле или измерение, над которым требуется произвести вычисление. Поля из нескольких слов заключаются в квадратные скобки [ ]. Поле заключается в круглые скобки функции агрегации. Например, Sum([Величина дохода, руб]);
Задать набор условий – в фигурных скобках перед полем. Например, Sum({…}[Величина дохода, руб]). Решить, сколько данных требуется включить или измерить, задав идентификаторы. Основные идентификаторы:
1 – Sum({1}[Величина дохода, руб]) – таблицы не будет подстраиваться под изменяемые фильтры на аналитической витрине и всегда будет отображаться фиксированное значение;

Анализ множеств. Синтаксис
Sum({$<[Отчетный период]={"201*"}>}[Величина дохода, руб])
Порядок добавления:
$ - Sum({$}[Величина дохода, руб]) – данные на элементе будут пересчитаны с учетом выбранного фильтр в витрине. Например, выбрали конкретного сотрудника – отобразится сумма дохода для сотрудника.;
1-$ - Sum({1-$}[Величина дохода, руб]) – данные на элементе будут пересчитаны без учета данных, выбранных в фильтре. Например, выбрали конкретного сотрудника – отобразится сумма дохода всех сотрудников, кроме выбранного;
Если не задать ничего, QlikView посчитает, что там $.
Задать условие (модификатор) – задать условие, на которое будет реагировать выражение. Задается в треугольных скобках внутри фигурных перед идентификатором. Например: Sum({$<>}[Величина дохода, руб])
Условия добавляются в формате <Условие 1, …, Условие N>
Каждое условие задается в формате [Параметр]={Значение}

Анализ множеств. Синтаксис
Sum({$<[Отчетный период]={"201*"}>}[Величина дохода, руб])
Порядок добавления:
Цифры в условиях можно задавать без “ ”. Символьные – с ‘ ’ (одинарные кавычки). Если требуется найти значение по вхождению, задать ограничение (больше, меньше) – требуются " " (двойные кавычки);
Если в условии ничего не задать, данный фильтр (выборка) не будет учитываться при формировании множества: ({$<[Отчетный период]=>}[Величина дохода, руб])
Также можно задавать:
несколько значений в условиях:
Sum({$<[Отчетный период]={2015, 2016}>} [Величина дохода, руб])
несколько условий:
Sum({$<[Отчетный период]={2015, 2016}, [ФИО сотрудника] = ‘Иванов’, ‘Петров’>}[Величина дохода, руб])
поиск по вхождению:
Sum({$<[ФИО Сотрудника]={"Петро*"}>}[Величина дохода, руб])
ограничение по граничному значению:
Sum({$<[Отчетный период]=“>2013”>}[Величина дохода, руб])
ограничение по диапазону:
Sum({$<[Отчетный период]={">2014<2016"}>}[Величина дохода, руб])

Задание на самостоятельное выполнение
Задания:
Добавить сводную таблицу;
Добавить два измерения;
Добавить два выражения: численное и графическое;
Задать нужные формулы для вычисления выражений;