Помогите написать выражение для построения графика в 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" за выбранный в списке месяц

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

Заранее благодарен.

admin

Добрый день.
Можете подготовить контрольный пример?
Таблицу-исходник в xls и рассчитанные показатели для сверки.

Дмитрий

Спасибо за оперативность. Конечно подготовлю. По готовности - выложу.

Дмитрий

Прикрепил исходный файл.

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

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

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

Еще раз спасибо за помощь.

admin

ОК, интересная задача.
Я так понимаю, затруднение в определении количества < компаний у которых  ВСЕ офисы имеют статус "NEW"> ?
Пока могу только ссылку на теорию дать
С практикой, только на выходных .

Дмитрий

Спасибо за ссылку - буду изучать.

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




admin

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

admin

Вопрос по параметру С.
Считаются все организации кто хотя бы раз имел ситуацию как для параметра B ?
Т.е. организации, имеющие хоть раз ситуацию, когда все офисы имели статус New в один из периодов идут в подсчет?

Дмитрий

Да, все верно. В параметре С считаются все компании, в которых хотя бы раз все офисы были со статусом New, за период с начала года (January 14) по выбранный месяц.
Грубо говоря,  необходимо посчитать сколько у нас появилось новых компаний.

admin

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

admin

#10
Вкратце, по задаче.
Первым делом, при загрузке следует преобразовать значение поля 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()))

Дмитрий

#11
Спасибо огромное. С контрольными показателями действительно - я напутал. У вас все верно.

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

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

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. При выборе любого другого периода на графике лишь "Нет данных для отображения" :(

В чем может быть дело?

admin

 :o
Я в рабочих перерывах пишу и не все еще выложил, будут нюансы.
Но если работает, значит на верном пути  :)

Дмитрий


admin

#14
Ниже снимок отладочной таблицы.
Я предпочитаю разбивать формулы, по возможности. Проще для понимания ( и себе и тем кто с кодом будет работать).
Поэтому ввожу в скрипт переменные:

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]
Должен отметить, что это лишь один из вариантов, для таблицы. Стоит изменить сортировку и все будет иначе  ;).

Яндекс.Метрика