Неофициальный форум пользователей Qlik Sense & Qlikview

Для разработчиков => Вопросы по Qlik Sense & QlikView => Тема начата: Дмитрий от 26 февраля 2015, 02:36:47

Название: Помогите написать выражение для построения графика в Qlikview
Отправлено: Дмитрий от 26 февраля 2015, 02:36:47
Добрый день.

Только начал самостоятельно осваивать Qlick View - соответственно пока имею лишь поверхностное представление о его возможностях. Заранее прошу прощения за, как кому то может показаться, "ламерские" вопросы.

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

Коэффициент достаточно "замороченный". Считается как ((A+B)-C) / (D+E), где:

A - кол-во  компаний с ХОТЯ БЫ ОДНИМ  статусом офиса "ACTIVE" за месяц January 14
B - кол-во  компаний у которых  ВСЕ офисы имеют статус "NEW" за месяц January 14
C - кол-во  компаний у которых  ВСЕ офисы имеют статус "NEW" за период с January 14 по выбранный в списке месяц включительно
D - кол-во  компаний с ХОТЯ БЫ ОДНИМ  статусом офиса "ACTIVE" за выбранный в списке месяц
E - кол-во  компаний у которых  ВСЕ офисы имеют статус "NEW" за выбранный в списке месяц

Буду очень признателен за любую помощь и советы относительно того как можно решить эту задачу.

Заранее благодарен.
Название: Re: Помогите написать выражение для построения графика
Отправлено: admin от 26 февраля 2015, 03:31:04
Добрый день.
Можете подготовить контрольный пример?
Таблицу-исходник в xls и рассчитанные показатели для сверки.
Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 26 февраля 2015, 03:35:59
Спасибо за оперативность. Конечно подготовлю. По готовности - выложу.
Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 26 февраля 2015, 05:10:12
Прикрепил исходный файл.

Уточнение - немного напутал с формулой коэффициента. Правильный вариант (((D+E)-C) / (A+B))*100%

В итоге, если в списке выбрать December 14, то должны получиться след.значения коэффициента:

FEDERAL (86%)
INDEPENDED (88%)
REGIONAL (52%)

Еще раз спасибо за помощь.
Название: Re: Помогите написать выражение для построения графика
Отправлено: admin от 27 февраля 2015, 08:26:12
ОК, интересная задача.
Я так понимаю, затруднение в определении количества < компаний у которых  ВСЕ офисы имеют статус "NEW"> ?
Пока могу только ссылку на теорию дать (http://help.qlik.com/sense/ru-RU/online/#../Subsystems/Hub/Content/ChartFunctions/SetAnalysis/SetModifiers.htm%3FTocPath%3D%25D0%25A0%25D0%25B0%25D0%25B1%25D0%25BE%25D1%2582%25D0%25B0%2520%25D1%2581%2520Qlik%2520Sense%7C%25D0%25A1%25D0%25B8%25D0%25BD%25D1%2582%25D0%25B0%25D0%25BA%25D1%2581%25D0%25B8%25D1%2581%2520%25D1%2581%25D0%25BA%25D1%2580%25D0%25B8%25D0%25BF%25D1%2582%25D0%25B0%2520%25D0%25B8%2520%25D1%2584%25D1%2583%25D0%25BD%25D0%25BA%25D1%2586%25D0%25B8%25D0%25B8%2520%25D0%25B4%25D0%25B8%25D0%25B0%25D0%25B3%25D1%2580%25D0%25B0%25D0%25BC%25D0%25BC%25D1%258B%7C%25D0%2592%25D1%258B%25D1%2580%25D0%25B0%25D0%25B6%25D0%25B5%25D0%25BD%25D0%25B8%25D1%258F%2520%25D0%25B2%25D0%25B8%25D0%25B7%25D1%2583%25D0%25B0%25D0%25BB%25D0%25B8%25D0%25B7%25D0%25B0%25D1%2586%25D0%25B8%25D0%25B8%7C%25D0%259C%25D0%25BE%25D0%25B4%25D0%25B8%25D1%2584%25D0%25B8%25D0%25BA%25D0%25B0%25D1%2582%25D0%25BE%25D1%2580%25D1%258B%2520%25D0%25BC%25D0%25BD%25D0%25BE%25D0%25B6%25D0%25B5%25D1%2581%25D1%2582%25D0%25B2%25D0%25B0%7C_____0)
С практикой, только на выходных .
Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 27 февраля 2015, 11:50:26
Спасибо за ссылку - буду изучать.

Проблема у меня несколько шире - отсутствует понимание синтаксиса необходимого выражения в принципе (с SQL знаком предельно поверхностно) :)



Название: Re: Помогите написать выражение для построения графика
Отправлено: admin от 27 февраля 2015, 02:49:04
SQL там совсем немного.
В вашем случае надо правильно загрузить таблицу, с преобразованием значения периода в формат. пригодный для обработки.
Затем надо будет составить выражение. Оно довольное сложное, для новичка.
Но если разбить работу на этапы, то все окажется намного проще.
Попозже посмотрю.
Название: Re: Помогите написать выражение для построения графика
Отправлено: admin от 02 марта 2015, 10:52:53
Вопрос по параметру С.
Считаются все организации кто хотя бы раз имел ситуацию как для параметра B ?
Т.е. организации, имеющие хоть раз ситуацию, когда все офисы имели статус New в один из периодов идут в подсчет?
Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 02 марта 2015, 11:21:34
Да, все верно. В параметре С считаются все компании, в которых хотя бы раз все офисы были со статусом New, за период с начала года (January 14) по выбранный месяц.
Грубо говоря,  необходимо посчитать сколько у нас появилось новых компаний.
Название: Re: Помогите написать выражение для построения графика
Отправлено: admin от 03 марта 2015, 09:57:07
Дмитрий, извиняюсь за задержку.
Во вложении таблица с показателем по каждому типу и периоду, но на ваши контрольные цифры не выходит.
Посмотрите по таблице параметры, правильно рассчитаны или нет.
Название: Re: Помогите написать выражение для построения графика
Отправлено: admin от 03 марта 2015, 10:28:39
Вкратце, по задаче.
Первым делом, при загрузке следует преобразовать значение поля PERIOD в формат, пригодный для сортировки.

map1:
mapping LOAD PERIOD, dual(month(FirstDay) & '-' & year(FirstDay),year(FirstDay) & num(month(FirstDay), '00')) as 'Месяц-Год' inline [
PERIOD,FirstDay
January 14, 01.01.2014
February 14, 01.02.2014
March 14, 01.03.2014
April 14, 01.04.2014
May 14, 01.05.2014
June 14, 01.06.2014
July 14, 01.07.2014
August 14, 01.08.2014
September 14, 01.09.2014
October 14, 01.10.2014
November 14, 01.11.2014
December 14, 01.12.2014
];

tmp1:
LOAD num(ApplyMap('map1',PERIOD,'--')) as PERIODNum,
ApplyMap('map1',PERIOD,'--') as PERIOD,
PERIOD as PERIODOld,
     TYPE,
     COMPANY,
     [CITY OFFICE],
     STATUS
FROM
[таблицы.xlsx]
(ooxml, embedded labels, table is Лист1);

Три поля с периодом для понимания.
Затем по, условию задачи нам надо будет высчитать сколько организаций имели офисов в статусе только NEW, т.е. не имея остальных статусов. На мой взгляд, проще всего добавить к колонкам таблицы флажок с количеством статусов для каждой организации.
Поэтому к вышеуказанному коду добавляем:

tmp2:
//NoConcatenate
left join LOAD PERIOD,
     TYPE,
     COMPANY,
Count(DISTINCT STATUS) as STATUS_ALL
Resident tmp1
Group by PERIOD,TYPE, COMPANY;

тем самым, мы упрощаем себе задачу в определении параметра E
count(DISTINCT {<STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY)
Для отладки выражений загрузим таблицу в приложение и создадим диаграмму в виде прямой таблицы.
Измерения - TYPE, PERIOD
Выражения, начнем с D
Нам надо подсчитать количество компаний, где есть хотя бы один статус ACTIVE хоть в одном офисе.
Языком функций это будет выглядеть так:
Count(DISTINCT {<STATUS={"ACTIVE"}>} COMPANY)
Для одного месяца:
Count(DISTINCT {<PERIODNum={'201401'},STATUS={"ACTIVE"}>} COMPANY)
Это у нас выходит параметр A
Далее, по аналогии выходим на параметр B
count(DISTINCT {<PERIOD={'201401'},STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY)
Параметр C представляет собой E, с ежемесячным накоплением по каждому TYPE.
RangeSum(Above(count(DISTINCT {<STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY),0,RowNo()))
Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 03 марта 2015, 03:12:33
Спасибо огромное. С контрольными показателями действительно - я напутал. У вас все верно.

Буду разбираться с премудростями редактирования скрипта ))

А из того, что прямо сейчас не понимаю:

1) все перечисленные вами правки скрипта внес
2) создал на листе список из PERIODNum
3) создал диаграмму(гистограмму, измерение TYPE и PERIOD) где прописал выражение:

((Count(DISTINCT {<STATUS={"ACTIVE"}>} COMPANY) + Count(DISTINCT {<STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY))- RangeSum(Above(Count(DISTINCT {<STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY),0,RowNo())))/(Count(DISTINCT {<PERIODNum={'201401'},STATUS={"ACTIVE"}>} COMPANY) - Count(DISTINCT {<PERIOD={'201401'},STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY))

В итоге, все идеально работает, НО только если в списке выбрать период 201401. При выборе любого другого периода на графике лишь "Нет данных для отображения" :(

В чем может быть дело?
Название: Re: Помогите написать выражение для построения графика
Отправлено: admin от 03 марта 2015, 03:52:05
 :o
Я в рабочих перерывах пишу и не все еще выложил, будут нюансы.
Но если работает, значит на верном пути  :)
Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 03 марта 2015, 04:01:48
Сорри за назойливость :) 
Название: Re: Помогите написать выражение для построения графика
Отправлено: admin от 03 марта 2015, 07:45:26
Ниже снимок отладочной таблицы.
Я предпочитаю разбивать формулы, по возможности. Проще для понимания ( и себе и тем кто с кодом будет работать).
Поэтому ввожу в скрипт переменные:

set vA=RangeSum(Above(Count(DISTINCT {<PERIODNum={'201401'},STATUS={"ACTIVE"}>} COMPANY),0,RowNo() ));
set vB=RangeSum(Above(count(DISTINCT {<PERIODNum={'201401'},STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY),0,RowNo()));
set vC=RangeSum(Above(count(DISTINCT {<STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY),0,RowNo()));
set vD=Count(DISTINCT {<STATUS={"ACTIVE"}>} COMPANY);
set vE=count(DISTINCT {<STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY);


И использую их в объектах и конечной формуле.
[smg id=76 type=preview]
[smg id=77 type=preview]
Должен отметить, что это лишь один из вариантов, для таблицы. Стоит изменить сортировку и все будет иначе  ;).
Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 03 марта 2015, 10:43:04
Бомба...Правда пока не совсем понятен синтаксис и принцип работы некоторых функций, но буду учить мат.часть :)

Название: Re: Помогите написать выражение для построения графика
Отправлено: admin от 04 марта 2015, 08:43:51
Спрашивайте. Поэтапно разберем, заодно может кто другое решение предложит.
Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 04 марта 2015, 11:25:04
Благодарю.

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

set vB=RangeSum(Above(count(DISTINCT {<PERIODNum={'201401'},STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY),0,RowNo()))

Как я понимаю:

count посчитала уникальные значения компаний, удовлетворяющих условиям:
     - значение PERIODNum = 201401
     - значение STATUS = NEW
     - значение STATUS_ALL=1 (т.к. у компании есть только один вид статуса - "NEW")

А вот дальше наступает либо непонимание, либо недопонимание :)

- Above, согласно теории, возвращает значения над n-1 строками (так как считаем от текущей строки и далее RowNo, который возвращает номер строки). Т.е. таким образом мы проверяем все возможные статусы одной компании?
- А Rangesum получается сложил все уникальные диапазоны(компании), которые отвечали вышеперечисленным условиям?

И еще один момент, который не очень понял из пособия, что именно означает знак $ (например перед переменной)?
Название: Re: Помогите написать выражение для построения графика
Отправлено: admin от 04 марта 2015, 01:56:21
Формулы написаны для таблицы.
Таблица строится по измерениям, и каждое выражение строится по этим измерениям.
Т.к. в фильтре уже указан период, то это выражение для других строк не будет давать результата.
Для того, чтобы заполнить поле по всем строкам используем связку RangeSum(Above(),0,Rowno())
Создайте таблицу с Type , Period и создайте три выражения:
RangeSum(Above(count(DISTINCT {<PERIODNum={'201401'},STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY),0,RowNo()))
Above(count(DISTINCT {<PERIODNum={'201401'},STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY),0,RowNo())
count(DISTINCT {<PERIODNum={'201401'},STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY)
Название: Re: Помогите написать выражение для построения графика
Отправлено: admin от 04 марта 2015, 02:00:07
По поводу знака доллара $ (http://help.qlik.com/sense/ru-RU/online/#../Subsystems/Hub/Content/Scripting/DollarSignExpansions.htm%3FTocPath%3D%25D0%25A0%25D0%25B0%25D0%25B1%25D0%25BE%25D1%2582%25D0%25B0%2520%25D1%2581%2520Qlik%2520Sense%7C%25D0%2597%25D0%25B0%25D0%25B3%25D1%2580%25D1%2583%25D0%25B7%25D0%25BA%25D0%25B0%2520%25D0%25B4%25D0%25B0%25D0%25BD%25D0%25BD%25D1%258B%25D1%2585%7C%25D0%259B%25D1%2583%25D1%2587%25D1%2588%25D0%25B8%25D0%25B5%2520%25D1%2581%25D0%25BF%25D0%25BE%25D1%2581%25D0%25BE%25D0%25B1%25D1%258B%2520%25D0%25BC%25D0%25BE%25D0%25B4%25D0%25B5%25D0%25BB%25D0%25B8%25D1%2580%25D0%25BE%25D0%25B2%25D0%25B0%25D0%25BD%25D0%25B8%25D1%258F%2520%25D0%25B4%25D0%25B0%25D0%25BD%25D0%25BD%25D1%258B%25D1%2585%7C%25D0%25A0%25D0%25B0%25D1%2581%25D1%2588%25D0%25B8%25D1%2580%25D0%25B5%25D0%25BD%25D0%25B8%25D1%258F%2520%25D1%2581%25D0%25BE%2520%25D0%25B7%25D0%25BD%25D0%25B0%25D0%25BA%25D0%25BE%25D0%25BC%2520%25D0%25B4%25D0%25BE%25D0%25BB%25D0%25BB%25D0%25B0%25D1%2580%25D0%25B0%7C_____0)
Проще говоря - так надо :). Хочешь использовать переменную - ставь $(ИмяПеременной). Работает везде, в скрипте и в выражениях.( в Qlikview и Qlik Sense)
Название: Re: Помогите написать выражение для построения графика
Отправлено: Chernov от 11 марта 2015, 09:34:25
А я бы на самом деле рекомендовал все расчеты проводить на этапе загрузки.

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

Предлагаю вариант такой:
значение A и значение Б - неизменяемые величины, их рассчитываем и помещаем в переменные, дабы не убивать память и проц их расчетом на каждую строчку.

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

значение C - рассчитать на основе данных из таблиц E и D.

как бы, это в кратце и при выводе таблицы будет мизерный расчет значений, нежели высчитывать по 16к строчек при выводе.
Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 11 марта 2015, 01:47:38
Ого...С моим знанием написания скрипта - буду анализировать неделю :) Спасибо за участие!


Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 25 марта 2015, 04:01:11
Всем здравствовать.

Спасибо за помощь - задача решена.

Но возникла очередная задача на основе все той же таблицы. Причем,добавился  еще один столбец (назовем его "Universe"), где проставлен числовой показатель (варьируется от 0 до n, может повторятся ).

Теперь необходимо подсчитать количество уникальных компаний ТОЛЬКО со статусом "CLOSED" и ТОЛЬКО если хотя бы один показатель "Universe" ,у этой компании, не равен нулю.

В очередной раз зашел в тупик :) Как посчитать все компании со статусом "CLOSED"  понимаю, а вот как сформировать условие еще  и с хотя бы одним ненулевым "Universe''  - увы...

Надеюсь на помощь знатоков.

Заранее благодарен.
Название: Re: Помогите написать выражение для построения графика
Отправлено: admin от 25 марта 2015, 04:14:24
Интересная у Вас работа, :) люблю головоломки.
Но с решением чуть попозже, ОК.
Может кто-то из читателей форума подключится?
Название: Re: Помогите написать выражение для построения графика
Отправлено: bibis от 26 марта 2015, 09:31:26
Я видимо что-то не понимаю, но в чем проблема использовать
if (Sum(Univers)>0, ...... )  ?

Т.е. если там неотрицательные числа, то логично, что 0 будет только если там одни нули.
Если  числа могут быть отрицательными, то:
if (Sum(fabs(Univers))>0, ...... )
Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 20 мая 2015, 06:39:42
Знатоки, извините, но я снова к вам за помощью...

Никак не могу разобраться в следующей проблеме :

Есть таблица (пример прилагаю). Соответственно мне необходимо посчитать сколько в месяце и канале сбыта существует компаний, у которых:
1) Только статус "CLOSED" и хотя бы один из показателей Universe больше ноля.
2) Одновременно есть как статус "CLOSED" так и "DUMMY"(произвольное количество и соотношение) и хотя бы у одного "CLOSED" Universe больше ноля.

Соответственно в моем примере должно получиться 3 компании в каждый из месяцев (компании 1,3 и 5).

Возможно ли это решить каким либо выражением при построении диаграммы по измерениям [Country Chanel] и [Period] ?

Как обычно  - буду признателен, если научите уму-разуму.

P.S. Прошу прощения, если не совсем внятно описал задачу...
Название: Re: Помогите написать выражение для построения графика
Отправлено: admin от 21 мая 2015, 08:59:34
День добрый.
Какие варианты решений рассматривали?
Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 21 мая 2015, 09:55:15
Изначально ввел переменную vX = count(DISTINCT {<[Company Name ]>} [Shop Changes]), чтобы подсчитать количество уникальных статусов у компании.

Затем построил диаграмму, по измерениям [Company Name] и Period, с выражением:

IF ($(vX)=1,count(distinct{<[Shop Changes]={'CLOSED'},Universe-={'0'}>} [Company Name]),0)

Естественно , что посчитались только компании где все статусы "CLOSED" и хотя бы один из них с Universe больше 0.

А вот как просчитать варианты, где среди "CLOSED" попадаются еще и "DUMMY", я понять не могу :(

Плюс также вопрос, когда к "CLOSED"  и "DUMMY" добавляются другие статусы( "ACTIVE" например)...
Название: Re: Помогите написать выражение для построения графика
Отправлено: bibis от 21 мая 2015, 10:44:45
Цитата: Дмитрий от 21 мая  2015, 09:55:15  

А вот как просчитать варианты, где среди "CLOSED" попадаются еще и "DUMMY", я понять не могу :(

Плюс также вопрос, когда к "CLOSED"  и "DUMMY" добавляются другие статусы( "ACTIVE" например)...

Думается функции      substringcount( concat( [distinct] ....
вас спасет, но уверен, что этот подход не оптимален.
Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 21 мая 2015, 12:49:34
Цитата: bibis от 21 мая  2015, 10:44:45  
Цитата: Дмитрий от 21 мая  2015, 09:55:15  

А вот как просчитать варианты, где среди "CLOSED" попадаются еще и "DUMMY", я понять не могу :(

Плюс также вопрос, когда к "CLOSED"  и "DUMMY" добавляются другие статусы( "ACTIVE" например)...

Думается функции      substringcount( concat( [distinct] ....
вас спасет, но уверен, что этот подход не оптимален.


Супер, с помощью  concat удалось решить проблему, по крайней мере частично. Буду дальше экспериментировать.

Спасибо!
Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 22 мая 2015, 04:23:49
Мое почтение.

Очередной тупик (видимо и меня можно так назвать :) )

В таблице след.поля: PERIOD,[Country channel],[Company name / Имя компании],[SHOP CHANGES], Universe

Есть выражение в диаграмме (измерения : [Country channel],[PERIOD],[Company name/ Имя компании]):

=IF(concat(distinct{<[Company name / Имя компании]>}[SHOP CHANGES])='NEW',IF(sum(Universe)<>'0', count(distinct [Company name / Имя компании]),0),0) + IF(concat(distinct{<[Company name / Имя компании]>}[SHOP CHANGES])='CLOSEDNEW',IF((sum(Universe)<>'0'), count(distinct [Company name / Имя компании]),0),0)

Как итог выдает след.график - см.вложение.

Хочу получить график где значения каждого месяца будут идти с нарастающим итогом (т.е. 201501 -2, 201502 -6, 201503- 9.

Предполагаю, что решение связано с Rangesum(Above(... , но как ни стараюсь его применить - результат далек от правильного :(

В очередной раз буду признателен за любое содействие.
Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 25 мая 2015, 01:40:57
Неужели нет решения ?..
Название: Re: Помогите написать выражение для построения графика
Отправлено: admin от 25 мая 2015, 01:45:59
Как работает опция для выражения "с накоплением" ?
Может это проще будет?
Название: Re: Помогите написать выражение для построения графика
Отправлено: Дмитрий от 25 мая 2015, 02:49:32
К сожалению, опции "Полное накопление"и "Накапливать" никакого воздействия не дают...
Название: Re: Помогите написать выражение для построения графика
Отправлено: admin от 25 мая 2015, 03:52:38
Дмитрий, ну не так просто разобраться в работе модели данных и выражении, даже если оно будет написано так:

=IF( concat(distinct{<[Company name / Имя компании]>}[SHOP CHANGES])='NEW',
IF(sum(Universe)<>'0',
count(distinct [Company name / Имя компании]),
0),
0)+
IF(concat(distinct{<[Company name / Имя компании]>}[SHOP CHANGES])='CLOSEDNEW',
IF((sum(Universe)<>'0'),
count(distinct [Company name / Имя компании]),
0),
0)

BIbis правильно отметил, что вариант не будет оптимальным.
Думаю, надо смотреть в сторону работу с анализом множеств и расширенного агрегирования функцией aggr().