Добрый день.
Только начал самостоятельно осваивать Qlick View - соответственно пока имею лишь поверхностное представление о его возможностях. Заранее прошу прощения за, как кому то может показаться, "ламерские" вопросы.
Суть задачи: имею загруженную таблицу (см.рис). Необходимо, чтобы при выборе из списка того или иного месяца на гистограмме или в таблице отражался определенный коэффициент для каждого TYPE.
Коэффициент достаточно "замороченный". Считается как ((A+B)-C) / (D+E), где:
A - кол-во компаний с ХОТЯ БЫ ОДНИМ статусом офиса "ACTIVE" за месяц January 14
B - кол-во компаний у которых ВСЕ офисы имеют статус "NEW" за месяц January 14
C - кол-во компаний у которых ВСЕ офисы имеют статус "NEW" за период с January 14 по выбранный в списке месяц включительно
D - кол-во компаний с ХОТЯ БЫ ОДНИМ статусом офиса "ACTIVE" за выбранный в списке месяц
E - кол-во компаний у которых ВСЕ офисы имеют статус "NEW" за выбранный в списке месяц
Буду очень признателен за любую помощь и советы относительно того как можно решить эту задачу.
Заранее благодарен.
Добрый день.
Можете подготовить контрольный пример?
Таблицу-исходник в xls и рассчитанные показатели для сверки.
Спасибо за оперативность. Конечно подготовлю. По готовности - выложу.
Прикрепил исходный файл.
Уточнение - немного напутал с формулой коэффициента. Правильный вариант (((D+E)-C) / (A+B))*100%
В итоге, если в списке выбрать December 14, то должны получиться след.значения коэффициента:
FEDERAL (86%)
INDEPENDED (88%)
REGIONAL (52%)
Еще раз спасибо за помощь.
ОК, интересная задача.
Я так понимаю, затруднение в определении количества < компаний у которых ВСЕ офисы имеют статус "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)
С практикой, только на выходных .
Спасибо за ссылку - буду изучать.
Проблема у меня несколько шире - отсутствует понимание синтаксиса необходимого выражения в принципе (с SQL знаком предельно поверхностно) :)
SQL там совсем немного.
В вашем случае надо правильно загрузить таблицу, с преобразованием значения периода в формат. пригодный для обработки.
Затем надо будет составить выражение. Оно довольное сложное, для новичка.
Но если разбить работу на этапы, то все окажется намного проще.
Попозже посмотрю.
Вопрос по параметру С.
Считаются все организации кто хотя бы раз имел ситуацию как для параметра B ?
Т.е. организации, имеющие хоть раз ситуацию, когда все офисы имели статус New в один из периодов идут в подсчет?
Да, все верно. В параметре С считаются все компании, в которых хотя бы раз все офисы были со статусом New, за период с начала года (January 14) по выбранный месяц.
Грубо говоря, необходимо посчитать сколько у нас появилось новых компаний.
Дмитрий, извиняюсь за задержку.
Во вложении таблица с показателем по каждому типу и периоду, но на ваши контрольные цифры не выходит.
Посмотрите по таблице параметры, правильно рассчитаны или нет.
Вкратце, по задаче.
Первым делом, при загрузке следует преобразовать значение поля 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()))
Спасибо огромное. С контрольными показателями действительно - я напутал. У вас все верно.
Буду разбираться с премудростями редактирования скрипта ))
А из того, что прямо сейчас не понимаю:
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. При выборе любого другого периода на графике лишь "Нет данных для отображения" :(
В чем может быть дело?
:o
Я в рабочих перерывах пишу и не все еще выложил, будут нюансы.
Но если работает, значит на верном пути :)
Сорри за назойливость :)
Ниже снимок отладочной таблицы.
Я предпочитаю разбивать формулы, по возможности. Проще для понимания ( и себе и тем кто с кодом будет работать).
Поэтому ввожу в скрипт переменные:
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]
Должен отметить, что это лишь один из вариантов, для таблицы. Стоит изменить сортировку и все будет иначе ;).
Бомба...Правда пока не совсем понятен синтаксис и принцип работы некоторых функций, но буду учить мат.часть :)
Спрашивайте. Поэтапно разберем, заодно может кто другое решение предложит.
Благодарю.
Думаю если разберусь в принципе вычисления нижеприведенной переменной то, автоматически, пойму и остальные:
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 получается сложил все уникальные диапазоны(компании), которые отвечали вышеперечисленным условиям?
И еще один момент, который не очень понял из пособия, что именно означает знак $ (например перед переменной)?
Формулы написаны для таблицы.
Таблица строится по измерениям, и каждое выражение строится по этим измерениям.
Т.к. в фильтре уже указан период, то это выражение для других строк не будет давать результата.
Для того, чтобы заполнить поле по всем строкам используем связку 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)
По поводу знака доллара $ (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)
А я бы на самом деле рекомендовал все расчеты проводить на этапе загрузки.
Набросал за полчасика вариант скрипта загрузки данных, во вложении код, с описаниями.
Предлагаю вариант такой:
значение A и значение Б - неизменяемые величины, их рассчитываем и помещаем в переменные, дабы не убивать память и проц их расчетом на каждую строчку.
значение E и D - рассчитать по аналогии, но сделать это нужно в цикле на каждый месяц, и поместить полученные результаты в отдельную таблицу.
оставить связи с основной таблицей по периоду.
значение C - рассчитать на основе данных из таблиц E и D.
как бы, это в кратце и при выводе таблицы будет мизерный расчет значений, нежели высчитывать по 16к строчек при выводе.
Ого...С моим знанием написания скрипта - буду анализировать неделю :) Спасибо за участие!
Всем здравствовать.
Спасибо за помощь - задача решена.
Но возникла очередная задача на основе все той же таблицы. Причем,добавился еще один столбец (назовем его "Universe"), где проставлен числовой показатель (варьируется от 0 до n, может повторятся ).
Теперь необходимо подсчитать количество уникальных компаний ТОЛЬКО со статусом "CLOSED" и ТОЛЬКО если хотя бы один показатель "Universe" ,у этой компании, не равен нулю.
В очередной раз зашел в тупик :) Как посчитать все компании со статусом "CLOSED" понимаю, а вот как сформировать условие еще и с хотя бы одним ненулевым "Universe'' - увы...
Надеюсь на помощь знатоков.
Заранее благодарен.
Интересная у Вас работа, :) люблю головоломки.
Но с решением чуть попозже, ОК.
Может кто-то из читателей форума подключится?
Я видимо что-то не понимаю, но в чем проблема использовать
if (Sum(Univers)>0, ...... ) ?
Т.е. если там неотрицательные числа, то логично, что 0 будет только если там одни нули.
Если числа могут быть отрицательными, то:
if (Sum(fabs(Univers))>0, ...... )
Знатоки, извините, но я снова к вам за помощью...
Никак не могу разобраться в следующей проблеме :
Есть таблица (пример прилагаю). Соответственно мне необходимо посчитать сколько в месяце и канале сбыта существует компаний, у которых:
1) Только статус "CLOSED" и хотя бы один из показателей Universe больше ноля.
2) Одновременно есть как статус "CLOSED" так и "DUMMY"(произвольное количество и соотношение) и хотя бы у одного "CLOSED" Universe больше ноля.
Соответственно в моем примере должно получиться 3 компании в каждый из месяцев (компании 1,3 и 5).
Возможно ли это решить каким либо выражением при построении диаграммы по измерениям [Country Chanel] и [Period] ?
Как обычно - буду признателен, если научите уму-разуму.
P.S. Прошу прощения, если не совсем внятно описал задачу...
День добрый.
Какие варианты решений рассматривали?
Изначально ввел переменную 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" например)...
Цитата: Дмитрий от 21 мая 2015, 09:55:15
А вот как просчитать варианты, где среди "CLOSED" попадаются еще и "DUMMY", я понять не могу :(
Плюс также вопрос, когда к "CLOSED" и "DUMMY" добавляются другие статусы( "ACTIVE" например)...
Думается функции substringcount( concat( [distinct] ....
вас спасет, но уверен, что этот подход не оптимален.
Цитата: bibis от 21 мая 2015, 10:44:45
Цитата: Дмитрий от 21 мая 2015, 09:55:15
А вот как просчитать варианты, где среди "CLOSED" попадаются еще и "DUMMY", я понять не могу :(
Плюс также вопрос, когда к "CLOSED" и "DUMMY" добавляются другие статусы( "ACTIVE" например)...
Думается функции substringcount( concat( [distinct] ....
вас спасет, но уверен, что этот подход не оптимален.
Супер, с помощью concat удалось решить проблему, по крайней мере частично. Буду дальше экспериментировать.
Спасибо!
Мое почтение.
Очередной тупик (видимо и меня можно так назвать :) )
В таблице след.поля: 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(... , но как ни стараюсь его применить - результат далек от правильного :(
В очередной раз буду признателен за любое содействие.
Неужели нет решения ?..
Как работает опция для выражения "с накоплением" ?
Может это проще будет?
К сожалению, опции "Полное накопление"и "Накапливать" никакого воздействия не дают...
Дмитрий, ну не так просто разобраться в работе модели данных и выражении, даже если оно будет написано так:
=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().